Text to Number conversion

[Ifono78bezeqint]

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

[#2]

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

[#3]

@Ifono78bezeqint 

Could you copy a few of the problem values into an empty new workbook and attach that to a reply?

[#4]

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 "-".

[#5]

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!

[#6]

Hi@Hans Vogelaar 

Attached is a sample workbook with a couple of problem values.

[#7 · Accepted solution]

@Ifono78bezeqint 

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

[#8]

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!

[#9]

@Sergei Baklan 

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!

[#10]

@Ifono78bezeqint , you are welcome