Excel How-To

How to Convert Text to Numbers in Excel

Numbers stored as text break SUM and lookups. Convert them with the error tag, VALUE, or Text to Columns.

Step by step

1
Spot the issue
Text-numbers align left and may show a green triangle.
2
Quick fix
Select the cells, click the warning icon, and choose Convert to Number.
3
Formula fix
Use =VALUE(A2), or multiply by 1: =A2*1.

=VALUE(A2)

Paste Special works on a whole range: copy an empty cell, select the text-numbers, then choose Paste Special ▸ Add.
A leading apostrophe and data imported from CSV files are the most common sources of numbers stored as text.

Why won't my numbers add up?

They're text — convert them first; SUM ignores text values.

How do I convert a whole column at once?

Select the column, go to Data ▸ Text to Columns and click Finish. Excel re-reads every cell and turns the text into numbers.

Why does VALUE return #VALUE!?

The text contains characters that are not part of a number, such as spaces or currency letters. Remove them first with TRIM, CLEAN or SUBSTITUTE.

How do I convert numbers that use a comma as the decimal separator?

Use =NUMBERVALUE(A2, ",", "."), which lets you state the decimal and group separators.

Functions