You can convert text to numbers in Excel in two ways. The quick way is the green triangle's Convert to Number command. The fallback is a VALUE formula whose results you paste back over the original column. Below is the order to try them, plus the fix for when the green triangle never shows up.
The steps come from Microsoft's support page on numbers stored as text. It lists Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, Excel 2016 and Excel Web App. The menu paths here follow the page's Windows instructions. Excel for the web may differ, and Microsoft has a separate page for Excel for the web.
Why numbers stored as text cause trouble
This usually happens after you paste from a CSV file, a web page or an accounting export. The cells look like numbers, but Excel treats them as text. Microsoft warns that this can cause unexpected results, such as a formula appearing in the cell instead of its result.
Method 1: Use the green triangle's Convert to Number
Microsoft says Excel usually flags these cells with a green error indicator in the top-left corner of the cell. Try this method first.
- Select the cells you want to convert.
- Select the error indicator next to the selection, or press Alt+Shift+F10.
- Choose Convert to Number from the menu.
The green triangles should disappear once the cells become numbers.
No green triangle? Turn background error checking back on
If the cells clearly hold text but no error indicator appears, error checking may be switched off. Microsoft's fix is:
- Go to File > Options.
- Select Formulas.
- Under Error Checking, tick Enable background error checking.
Go back to the sheet and try Method 1 again.
Method 2: Convert with the VALUE function
Use this when Convert to Number isn't available or doesn't fix the data. The function's syntax, per Microsoft's VALUE function page, is:
VALUE(text)
In Microsoft's example, the text numbers are in column E and the helper column is F.
- Insert a new column next to the cells that hold text.
- In the first cell of the new column, type the formula below. Point it at the first cell holding a text number. Microsoft's example uses E23:
=VALUE(E23)
- Fill the formula down. Drag the lower-right corner of the cell when the cursor changes to a plus sign.
You can now use the new column as it is. To keep your sheet layout unchanged, paste the results back into the original column.
Paste the converted values back over the original column
If you copy and paste normally, you get formulas instead of numbers. Paste values instead:
- Select the cells that contain the new
VALUEformulas and press Ctrl+C. - Select the first cell of the original column.
- On the Home tab, select the arrow below Paste, then choose Paste Special > Values. The keyboard shortcut is Ctrl+Shift+V.
The original column now holds real numbers. The helper column has done its job, so you can delete it.
Check that the conversion worked
Totals and other calculations should now pick up the converted cells. If a PivotTable built on this data was counting a field instead of summing it, see how to fix PivotTable fields that count, not sum. Conditional totals like SUMIFS with several conditions also need the values to be real numbers.
If neither Convert to Number nor VALUE fixes your data, the problem is outside the scope of this guide, which covers only these two methods.
