Forum Discussion
Solved
Text to Number conversion
Recently I exported my bank statement to an Excel spreadsheet. The export worked fine except that negative values were exported as text not as numbers (bad practice by the bank programmers…). Tried t...
Perhaps you copy/pasted your numbers from web or like. Before each "minus" there is non-printable character (unicode 8206).
As variant conversion could be as
=IF(UNICODE(LEFT(B2))=8206,RIGHT(B2,LEN(B2)-1),B2)*1
In my experience, sometimes the "-" in imported data is not the same "-" on the keyboard that you are keying. Try going into a cell and selecting the "-" character, then copy it (ctrl+c), then escape, then do a find/replace - pasting (ctrl+v) the "-" character you copied from the data and replace it with the standard "-".
Hi JMB17 ,
It sounded promising, but unfortunately didn't work...
In the attached sample I retyped the value of cell B15 in the formula bar, but =VALUE() still evaluated to error.
Thanks!
Perhaps you copy/pasted your numbers from web or like. Before each "minus" there is non-printable character (unicode 8206).
As variant conversion could be as
=IF(UNICODE(LEFT(B2))=8206,RIGHT(B2,LEN(B2)-1),B2)*1Bingo!
I would have never thought about this...
BTW, the numbers are from an export option of an account statement by a big commercial bank...
Thanks Sergei!
