Home › Excel basics › Convert text to number

How to Convert Text to Numbers in Excel

When numbers are stored as text, Excel can't calculate with them: SUM ignores them, VLOOKUP doesn't find them and sorting goes wrong. The quickest fix: select the cells, click the yellow warning icon and choose Convert to Number. Below are five more methods for when that button doesn't appear.

Excel CSE · 3 min read

How to spot numbers stored as text

• The numbers sit on the left of the cell instead of the right.
• A small green triangle in the top-left corner of the cell.
• The status bar shows a Count but no Sum when you select them.
• =ISTEXT(A2) returns TRUE.

This usually happens after importing a CSV, copying from a website or a system export, or when the cell was formatted as Text before you typed.

1. Convert to Number (the warning icon)

1. Select all the cells with the green triangle. Start with a cell that has one.
2. Click the yellow ! icon next to the selection.
3. Choose Convert to Number.

Fast for a few thousand cells. With very large ranges it can take a while; use method 3 or 4.

2. The VALUE function

VALUE turns text that looks like a number into a real number, and ignores leading and trailing spaces: =VALUE(" 45 ") returns 45. A shorter way that does the same is the double minus: =--A2. Afterwards you can copy the results and paste them back as values.

3. Paste Special: multiply by 1

1. Type 1 in an empty cell and copy it.
2. Select the text numbers.
3. Press Ctrl+Alt+V, choose Multiply and click OK.

Multiplying forces Excel to treat the text as numbers, in place and without helper columns. Delete the 1 afterwards. More tricks in Paste Special.

4. Text to Columns

Select one column, go to Data › Text to Columns and click Finish straight away. Excel re-reads every cell and converts numbers and dates. This is the fastest method for a whole column, and it also fixes dates stored as text. See Text to Columns.

5. Remove hidden spaces

Text copied from a web page often contains a non-breaking space (character 160), which VALUE and TRIM don't remove. Use:

Or remove them in bulk: Ctrl+H, in "Find what" hold Alt and type 0160 on the number pad, leave "Replace with" empty, and click Replace All. For normal extra spaces, see TRIM.

6. Decimal commas and other separators

A file from another country may use 12,5 where your Excel expects 12.5. NUMBERVALUE lets you say which separators the text uses:

The second argument is the decimal separator in the text, the third the thousands separator. "1.250,75" becomes 1250.75. When opening a CSV, you can also set this up front in Data › From Text/CSV.

The other way round: number to text

Sometimes you need text, for instance to keep leading zeros in a postcode or product code. =TEXT(A2, "00000") turns 742 into "00742". Formatting a column as Text before typing also keeps the zeros.