Mutliplying text and number values Macro with mutliple conditions

**antimel** · 04-24-2007, 04:41 PM

I need a Macro to multiply text and numbers by 1,000. Here are the caveats. The text or numbers will appear as the following. There is a comma instead of a period because we are working with Latin American data and they're usage of commas and periods are different that ours.

"<5,00123"
"< 5,00123"
"5,00123 J"
5.000123 - this will appear as "5,000123" however that is only because it is formatted that way, the Excel value is still 5.000123

The values above after multiplied by 1000 should equal the following:
"<5.000,123"
"< 5.000,123"
"5.000,123 J"
5,000.123 - this will need to stay in number format and we could could simply change the format to match the Latin American comma period foramatting.

We also place "NA" or "Nor Sampled" or some other text in the cell. These would have to be returned as the same text.

Also, in the case of the following:

"<0.098"
should equal
"<98" and not include all the excess 0's at the end.

and
"<9.8" should equal
"<9.800" and not include all the excess 0's.

Ideally, I would like to hightlight all the cells that need to be changed and then run a macro that would perform the operation in place. This would be much easier than a UDF.

Thank you in advance,

Anthony