Forum Discussion
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 to convert the text to number with the recommended methods in Help but to no avail.
Then tried the function VALUE() with its argument the cell with the negative number as text. It returned a #VALUE! error. I selected the Show Calculation Steps from the error context menu and the text correctly appeared enclosed in double quotes. The next step evaluated to an error.
HOWEVER, if I manually typed the argument of the VALUE() function exactly as it appeared in Show Calculation Steps, it returned the correct number.
Here is an example: Assume that cell B2 has -180.00 as text. The formula in C2 =VALUE(B2) evaluates to #VALUE! error. The first step in Show Calculation Steps looks like VALUE(“-180.00”). The next step is an error.
If, however, I manually type in C2 =VALUE(“-180.00”) then it returns the correct answer as the negative number -180.00.
It drives me nuts!
Thanks,
Ilan
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
9 Replies
- 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)*1
Could you copy a few of the problem values into an empty new workbook and attach that to a reply?
Attached is a sample workbook with a couple of problem values.
- 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.
StoneKiwiHiStoneKiwi ,
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!
