Converting numbers from a Bank Statement to "real" numbers

[Mark McInerney]

Hi All,

I have copied and pasted numbers from an online bank statement.

They appear as numbers and I have formatted them as numbers but I can't do any calculations on them. They won't sum?

Is there a way to convert the numbers to "real" numbers, so as I can work with them???

Any help appreciated - Thanks - Mark.

 

[Trebor76]

Hi Mark,

Use the VALUE function to convert the text numbers to a value i.e. =VALUE(A2) will convert a string (text) number that is in A2 to its value. You can then copy and paste these as values back to their original location if need be.

HTH

Robert

 

[njimack]

You can also add zero or multiply by 1 to force a text number to be numeric.

 

[Mark McInerney]

Hi Robert,

Thanks For reply.

I get a #VALUE! when I tried this. The same when I multiply it by 1???

V Strange....

 

[Mark McInerney]

Hi Neil,

Thanks for you help. I am getting #VALUE! when i tried the =value(a2), and when I multiply it by 1?

 

[njimack]

You may have trailing or leading spaces in the cell, or non-printable characters. You probably need to use TRIM or CLEAN, and then multiply by 1.

 

[Mark McInerney]

Hi Neil.

Looks like you have it! How do i take the 2 leading blank values away??? Can I use the Left( ) function???

 

[njimack]

You can use LEFT but it would be easier to use TRIM or CLEAN (or even both, with one nested inside the other) in a helper column.