replace ' with null in text column

[mart10]

I am using Excel 2003. I inherited spreadsheet where bank account numbers were given a column format of number instead of text. Sometimes people have entered a bank account say 002456 as '002456 to show the leading zeros although the ' doesnt allways come before a zero

I want to change the column to text format but keep all leading zeros, so I need to do find a and replace ' with a null but it wont let me do it

Any ideas

 

[SkipVought]

Hi,

You need the leading apostrophy.

Conduct this simple experament.
[tt]
A1: [ENTER] '001234
B1: (format as TEXT) [ENTER] 001234
C1: =A1=B1
[/tt]
See what happens!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

absolutely nothing happened!

 

[SkipVought]

huh!?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

Nothing happened?
Are you sure your formula didn't evaluate to FALSE?


Randy

 

[SkipVought]

The formula should evaluate to TRUE!!!
[tt]
'001234 = 001234
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

If you still experience problems, you must realize that merely changing the FORMAT of a range to TEXT does absolutely nothing, because...

changing a cell's format changes NOTHING in the underlying value.

'001234 is already text even if the cell is not formatted as text.

001234 is already text because the cell WAS formatted as text before the text was entered.

1234 cannot be merely FORMATTED as TEXT and magically become TEXT. It must be converted to text. You could, in an unused column do...
[tt]
=TEXT(A1,"000000")
[/tt]
and copy down.

Then COPY this column, SELECT column A, and Edit > Paste Special -- VALUES. This will CONVERT every value in the column. and BTW, will remove the appostrophy, if it makes you feel any better.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

d'oh -- yes, of course, it's TRUE

Randy