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
Hi,
This is likely a formatting issue so it is hard to diagnose the exact cause without an example spreadsheet.
You are right that =VALUE should work.
You could try =TEXT(cellwithvalue,"####") to format it as a number.
If that doesn't work just attach an example spreadsheet that has the error. Remove any personal/sensitive information before sharing.
StoneKiwi
This is likely a formatting issue so it is hard to diagnose the exact cause without an example spreadsheet.
You are right that =VALUE should work.
You could try =TEXT(cellwithvalue,"####") to format it as a number.
If that doesn't work just attach an example spreadsheet that has the error. Remove any personal/sensitive information before sharing.
StoneKiwi
HiStoneKiwi ,
Please see the attached example.
In B17 I did =TEXT(B15,"####") as you've suggested. That worked fine. However, in D17 =VALUE(B17) still evaluates to error...
Thanks!