Macro Help - truncate text
 

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.

Macro Help - truncate text
 

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.

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


"TheJoneser" wrote:

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.

Macro Help - truncate text
 

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.

"sebastienm" wrote:

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.

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


"TheJoneser" wrote:

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.

Macro Help - truncate text
 

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


"TheJoneser" wrote:

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.

"sebastienm" wrote:

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.

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


"TheJoneser" wrote:

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.