Convert text to number format in Excel
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
Related videos on Youtube
Bob
Updated on June 04, 2022Comments
-
Bob almost 2 years
How can I convert text representations of numbers into Excel numbers, especially negative values? For example, the string "9,669.34" (without quotes) should become the number
9,669.34
and the string "2,553.57-" (again, without quotes) should become the number(2,553.57)
.When I used the formula
=SUBSTITUTE(A1,CHAR(160),"")+0
, it worked well, but only for positive values. I received the result#VALUE!
for all negative values. -
Bob over 13 yearsThe =VALUE(IF(RIGHT(A2,1)="-","-"&SUBSTITUTE(A2,"-",""))) only takes the negative value & not the positive value. How can I apply a formula that can convert both positive & negative values ? I hv hundreds of row to manage.
-
Bob over 13 yearsok, I found it. I can do with =VALUE(IF(RIGHT(A2,1)="-","-"&SUBSTITUTE(A21,"-",""),A2)) OR =IF(RIGHT(A2,1)="-",VALUE("-"&LEFT(A2,LEN(A2)-1)),VALUE(A2)). Thanks.