Jun 17 2021 12:57 PM - edited Jun 17 2021 01:04 PM
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
Jun 17 2021 01:20 PM
Jun 17 2021 01:21 PM
Could you copy a few of the problem values into an empty new workbook and attach that to a reply?
Jun 17 2021 09:01 PM
Jun 19 2021 04:40 AM
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!
Jun 19 2021 04:43 AM
Attached is a sample workbook with a couple of problem values.
Jun 19 2021 05:04 AM
SolutionPerhaps 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
Jun 19 2021 05:08 AM
Hi@StoneKiwi ,
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!
Jun 19 2021 05:27 AM
Bingo!
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!
Jun 19 2021 05:04 AM
SolutionPerhaps 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