How to Convert Text to Numbers in Excel

Numbers pasted from a website or exported from another system often arrive as text. They sit on the left of the cell, show a green triangle in the corner, and SUM treats them as zero. Any of the methods below turns them back into real numbers.

Use the warning icon

Select the cells with the green triangle. A small warning icon appears next to the selection. Click it and choose Convert to Number. Every selected cell becomes a number at once. If no triangle shows, go to File, Options, Formulas and tick Numbers formatted as text or preceded by an apostrophe under Error checking rules.

Run Text to Columns

Select one column of text numbers, then go to Data, Text to Columns and click Finish on the first screen. Excel re-enters every value as a number. This works on a single column at a time and also fixes dates stored as text.

Multiply by 1 with Paste Special

Type 1 in any empty cell and copy it. Select the text numbers, press Ctrl + Alt + V (Command + Control + V on a Mac), choose Multiply, and click OK. Multiplying forces Excel to treat the text as a number, and unlike Text to Columns this handles several columns in one go. Delete the 1 afterward.

Convert with VALUE in a helper column

=VALUE(A2)

• text is the cell holding the text number.

If A2 holds the text 1,250.00, the cell shows the number 1250. Fill the formula down, copy the results, and paste them over the originals with Paste Values.

Strip hidden characters first

Web pages often pad numbers with non-breaking spaces, which VALUE cannot read and TRIM alone cannot remove:

=VALUE(TRIM(SUBSTITUTE(A2, CHAR(160), "")))

• SUBSTITUTE replaces CHAR(160), the non-breaking space, with nothing.
• TRIM removes ordinary spaces at either end.
• VALUE converts what remains.

A cell holding " 1250 " with a non-breaking space in front shows 1250.

If the cells are formatted as Text. Changing the format to General on the Home tab does not convert existing entries by itself. After changing the format, run one of the methods above, or press F2 then Enter on each cell to re-enter it.

If VALUE returns #VALUE!. The cell contains something other than digits, a decimal point, or a thousands separator, such as a currency symbol or a stray letter. Remove it with SUBSTITUTE, for example =VALUE(SUBSTITUTE(A2, "$", "")).