Converting text to numbers

[Sibrulotte]

Hi,
my bank statement cannot come in through CSV, I have to copy and paste it from the web.
Amounts that come from this paste into excel look like what you'll find in colomn A.

I've tried three tests to return a number that can be added.

Yet I still have issues returning proper numerical values.
Please don't tell me to use value(), it's more intriquite than what you might think.

(*22*862,87*$)

value on this doesn't return a number. Substituting coma for a dot is fine, I've done it in the second colomn but,substitute on the space between 22 and 862, well it's not a space, I still haven't figured out what it is, so I can't substitute it.


I humbely hope someone has something.

[ChemistB]

Try this (assuming the values with () are supposed to be negative)

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",","."),"$",),CHAR(160),"")*1
Does that work for you?

EDIT: to avoid #Value errors
=IF(A2<>"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",","."),"$",),CHAR(160),"")*1,"")

[Søren Larsen]

Try this:

=LEFT(A2,LEN(A2)-3)*1

EDIT: Only works for positive values. If ChemistB's suggestion works, then stick to that.

[icestationzbra]

there is a formula that Ron Coderre had created, one that is for ages.

put this in C2 and drag-fill down:

Please Login or Register  to view this content.

[Sibrulotte]

And you sir,
have the right answer!
Thank you.

[ChemistB]

I wonder which "sir" had the right answer?

[icestationzbra]

isn't it obvious? there is only one "sir" here, rest are commoners...

[ChemistB]

My thoughts exactly