Data scrubbing is ruining my life

August 26, 2009 11:14 AM

Another stupid excel question.

I am trying to use custom under format cells to make sure that all contents in a column are 7 digits long. I am using "custom" and in the input, I am adding 0000000.

I then want to keep this format, all cells with 7 digits, but change the format to text.

I have tried to add a column next to this column and format it as text and cut and paste special using everything available. However, the format goes back to the original.

The answer is simple- cut and paste it into a notepad and then transfer it back, however I am trying to record this in a macro so this solution will not work. Please help me automate this annoying process.

posted by TheBones to Technology (5 answers total)

Best answer: Try using the text formula: =TEXT(A1,"0000000")

Then you can use past special/values to get rid of the formula.
posted by yarrow at 11:18 AM on August 26, 2009

Response by poster: I can't seem to get this to work, please help. I have added a new column next to column A and inserted the formula you posted: =TEXT(A1,"0000000") and all I get in return is the formula such as:

Reference =TEXT(A1,"0000000")
744 =TEXT(A1,"0000000")
750 =TEXT(A1,"0000000")
1355 =TEXT(A1,"0000000")

Any help would be greatly appreciated.
posted by TheBones at 11:35 AM on August 26, 2009

Response by poster: I'm an idiot, I got it to work: of course it isn't going to show up for headers, but as for the numbers, I didn't change to A2 and fill down. When I did, it worked automagically.

Thank you, thank you, thank you!
posted by TheBones at 11:38 AM on August 26, 2009

You're getting the formula in return because the new column is formatted as text. You should change the format of the new column to "General".

I'm not sure whether or not the formula will then do what you want -- but it will at least do something.
posted by Perplexity at 11:39 AM on August 26, 2009

Glad it worked. Good luck with the scrubbing!
posted by yarrow at 12:56 PM on August 26, 2009