Geek Answers Handbook

Convert text to number format in Excel

How to convert textual representations of numbers to Excel numbers, especially negative values? For example, the string "9,669.34" (without the quotes) should become the number 9,669.34 , and the string "2,553.57-" (again without the 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 got the result #VALUE! for all negative values.

+6
text format numbers excel
source share
2 answers

For (2,553.57) you can use VALUE , for example VALUE("(2,553.57)") .

Excel doesn't seem to recognize 2,553.57- as a valid number if it is a string, so assuming you have the value “2,553.57-” in A1, you will need to do a bit more work:

<stroke> = VALUE (IF (RIGHT (A2,1) = "-", "-" & SUBSTITUTE (A2, "-", ""))) strike>

EDIT

 =VALUE(IF(RIGHT(A2,1)="-","-"&SUBSTITUTE(A2,"-",""),A2))

On the Microsoft website:

  • The text can be in any of the recognized constant numbers, dates or times in Microsoft Excel. If the text is not in one of these formats, VALUE returns # COST! error value.
  • Normally, you don’t need to use the VALUE function in a formula because Excel automatically converts text to numbers as needed. This feature is provided for compatibility with other spreadsheet programs.

More information can be found on the Microsoft website: Value Function

+6
source share

If you have a column of these text numbers, select the column and use Data ► Data Tools ► Text to Columns ► Fixed Width ► Finish. The final negative signs will be used to determine the sign of the received number, the spaces (leading and / or ending) will be deleted, and you will be left with raw numbers to which you can apply the currency or number format.

If the column is initially formatted as Text, change it to General before applying the Text command to the columns .

+1
source share

More articles:


All Articles