How to convert text to number, especially negative value:-
9,669.34 to 9,669.34 2,553.57- to (2,553.57)
When I used this formula, =SUBSTITUTE(A1,CHAR(160),"")+0, it works well but only for positive value. I rcv'd #VALUE! for all negative values.
+1
A:
For (2,553.57), you can use VALUE, such as VALUE("(2,553.57)").
Excel doesn't seem to recognize 2,553.57- as a valid number when it is a string, so assuming you have a value of "2,553.57-" in A1, you would have to do a little more work:
=VALUE(IF(RIGHT(A2,1)="-","-"&SUBSTITUTE(A2,"-","")))
EDIT
=VALUE(IF(RIGHT(A2,1)="-","-"&SUBSTITUTE(A2,"-",""),A2))
From the Microsoft site:
- Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.
- You do not generally need to use the VALUE function in a formula because Excel automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet programs.
More information can be found at Microsoft's website: Value Function
LittleBobbyTables
2010-09-15 11:29:25
Bob
2010-09-16 01:05:52
You're right, I forget the FALSE portion of the IF statement
LittleBobbyTables
2010-09-16 01:25:07
Bob
2010-09-16 01:30:05
Glad to help! As a matter of courtesy, you might want to increase your question accept rate if peoples' answers have helped you.
LittleBobbyTables
2010-09-16 15:51:37