Convert numbers stored as text to numbers

In some cases, numbers in a worksheet are actually formatted and stored in cells as text, which can cause problems with calculations and sorting. This issue sometimes occurs after you import or copy data from another data source.

Three options for converting

  1. On the worksheet, select the cells that have a small green triangle in the upper-left corner. The green triangle is an error, indicating that the number is stored as text.

  2. Click the error button that appears next to the cells.

    Error icon

  3. On the menu, click Convert to Number.

    Convert to Number command

    This action converts the numbers that are stored as text back to numbers.

In this technique, you multiply each selected cell by 1 in order to force the conversion from a text-formatted number to a regular number. Because you're multiplying the contents of the cell by 1, the result in the cell looks identical. However, Excel actually replaces the text-based contents of the cell with a numerical equivalent.

  1. Select a blank cell.

  2. Press CTRL + 1.

  3. On the Number tab, click General, and then click OK.

  4. In the cell, type 1, and then press ENTER.

  5. Select the cell, and then press CTRL + C to copy the value.

  6. Select the cells or ranges of cells that contain the numbers stored as text that you want to convert.

  7. On the Home tab, click the arrow below Paste, and then click Paste Special.

  8. Under Operation, select Multiply, and then click OK.

Note: Some accounting programs display negative values as text, with the negative sign () to the right of the value. To convert the text string to a value, you must use a formula to return all the characters of the text string except the rightmost character (the negation sign), and then multiply the result by –1. For example, lets say "156–" is the value for cell A2. The following formula converts the text to the value –156: =LEFT(A2,LEN(A2)-1)*-1

In some scenarios, you don't have to convert numbers stored as text back to numbers, as described earlier in this article. Instead, you can just apply a number format to achieve the same result. For example, if you enter numbers in a workbook, and then format those numbers as text, you won't see a green error indicator appear in the upper-left corner of the cell. In this case, you can apply number formatting.

  1. Select the cells that contain the numbers that are stored as text.

  2. Press CTRL + 1.

  3. In the Category box, click the number format that you want to use.

    For this procedure to complete successfully, make sure that the numbers that are stored as text do not include extra spaces or nonprintable characters in or around the numbers. The extra spaces or characters sometimes occur when you copy or import data from a database or other external source. To remove extra spaces from multiple numbers that are stored as text, you can use the TRIM function or CLEAN function. The TRIM function removes spaces from text except for single spaces between words. The CLEAN function removes all nonprintable characters from text.

See Also

TEXT function

TRIM function

CLEAN function

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!