Excel converts text cell contents to numbers (strips leading zeros)???!!!

[Excel Guy]

I've verified that Excel 2000 and 2008 exhibit this behavior, but open
office does not.

Format a cell as "Text".

Enter something like "01234a" into the cell (must be some number with
a leading zero and some non-numeric character). Note that the cell
will contain exactly what you typed (the leading zero will be
maintained).

Now perform a global search/replace. Replace "a" with "" (in other
words, you want to delete the "a" in the above example). Note that
the "a" will disappear, and so will the leading zero. But the cell
will remain formatted as "text".

Is this a bug?

Is there some way to perform a global search/replace without the
leading zero being deleted?

 

[Gary''s Student]

Don't rely on the Text format.

Use the apostrophe. For example, if you enter:

'01a

in a cell and then use Find/Replace to remove the a, the leading zero will
be preserved.