Macro Help - truncate text

[Guest]

I am working with output from a corporate accounting system. When data is
exported to Excel for Canadian operations the numbers are always represented
as text in the following format C$125.44.

I can't figure out how to quickly convert the data from text to numbers. If
I remove the C then Excel recognizes a number. Is there a fuction that will
remove only the leftmost character of a string? Or must I write a macro that
can do so?

Thank you.

 

[Guest]

Hi,
To remove the C and get the underlying number:
- Select your column of data
- menu Edit > Replace
Find what: C
Replace with: <leave blank>

As a result, the entire column should be converted to a number.

 

[Guest]

Sweet! Didn't even think of that. I also found that the function MID would
accomplish it but only in one cell.

Thanks for your help.

 

[Guest]

You're right, you could use a formula, something like:
Say data is in A2:A100
- in B2: =VALUE(SUBSTITUTE(A2,"C",""))
---> ie, in A2, replace "C" by blank then convert to numeric value
- copy/paste B2 along the data in B2:B100

Regards,
Sébastien
<http://www.ondemandanalysis.com>