Prev: How do you automatically display a vba form in excel 2007
Next: Excel 2003 - Skip a cell
From: mduncan1153 on 21 Apr 2010 11:41
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.
--
mduncan1153
From: Don on 21 Apr 2010 14:19
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

"mduncan1153" wrote:

> 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.
> --
> mduncan1153
 | 
Pages: 1
Prev: How do you automatically display a vba form in excel 2007
Next: Excel 2003 - Skip a cell