Find/Replace Numbers stored as Text drops zeros

[mduncan1153]

This SEEMS to have changed when Office 2003 SP3 rolled out - as we USED to be
able to do this. I'm not ruling out OE (Operator Error) - although we've
tried this on different op. sys.; different computers; different Office 2003
installs, and, as stated above, we have been able to do this in the past....

We process millions of rows of data coming in from thousands of sources.
While we provide the 'required' format information, invariably we have to
'groom' some data.

Due to the fact that some cell entries can be text or number, the cells MUST
be formatted as 'text'. These are ALL 'text': 'V25.2'; '078.00'; '592.0';
'600.00'; '788.20'.

Here's the problem: When someone uses the letter 'o' instead of the number
'0' we need to find/replace the 'o' with a '0'. Because there are thousands
of variations of incorrect data filtering takes way too long. Find/replace
'o' with '0' - WHETHER or NOT we indicate that the Replace shoud be formatted
as text - ALWAYS drops the '0's. For example, the incorrect '592.o'
becomes '592' instead of '592.0'; '600.oo' becomes '600' instead of '600.00';
'788.2o' becomes '788.2' instead of '788.20'. We CANNOT use a number format
as some of these cells should have no decimals; some only 1 decimal; some
with 2 decimals - and the number format requires a specified number of
decimal places.

WHAT AM I MISSING??? Each incorrect worksheet can take hours to fix. I've
searched this (and other) websites for almost 6 months and can't find anyone
else who seems to be having this trouble.

ANY help you can provide is greatly appreciated. Again, these cells are
TEXT and must STAY formatted as TEXT without dropping any of the zeroes.

 

[Don]

have you tried formatting the column(s) as Text first before the find and
replace?

bit of concern for me when you have so much data. Sounds like it gets
narrowed down to a managable level for Excel 2003. 2007 is better but still
has some issues