6

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.

Improve this question

3 Answers 3

Reset to default
7

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The =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.
ok, 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.
3

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

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

Improve this answer

Comments

0

Another option: Select the column. Copy. Paste Special with "Multiply" option.

Then, as above, you can select Currency or Number in the droplist, decimal places, etc.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.