To convert a number stored as text in Excel for Microsoft 365 on Windows, select the cell, click the warning icon that appears beside it, and choose Convert to Number. If there is no warning icon, use Data > Text to Columns and finish with the column format set to General. These methods change the underlying value so Excel can calculate with it; changing the cell’s number format alone usually does not.
What You’ll Need
- An Excel workbook containing the cell or range you want to convert. If the information matters, save a copy first, especially before converting a large range.
- A quick way to tell whether the value is text: select the cell and check whether Excel shows a warning indicator, or try a simple calculation with the value. Text often sits left-aligned by default, while numbers sit right-aligned, but alignment can be changed, so treat it as a clue rather than proof.
- A decision about whether the data should really be numeric. Keep product codes, postal codes, account numbers, and other identifiers as text when leading zeros or exact digits matter.
Step-by-Step
- Check what is in the cell. Select the cell and look at the formula bar. A value such as 125 may be a real number, while an entry that looks like 125 but was imported with a leading apostrophe or as text may not work in a sum. You can also test a small sample in a blank cell with a basic calculation such as adding 1. If Excel treats the value as text, the calculation may return an error or fail to include it in a total. Do not convert blindly: 00125 might be a code that must retain its zeros, not the number 125.
- Use Convert to Number when Excel flags the cell. Select the affected cell. If Excel has identified a number stored as text, a small warning icon appears near it. Click the icon and choose Convert to Number. The warning should disappear, and the value should behave as a number in calculations. For a range, select the affected cells first; if Excel offers the conversion for the selection, choose the command once. If the warning menu is absent or the range contains mixed content, use the next method instead. The warning icon can also offer other options, so read the menu rather than selecting an item by position.
- Convert a range with Text to Columns. Select the cells that contain the text-formatted numbers, without selecting unrelated columns. On the ribbon, choose Data > Text to Columns. In the wizard, choose Delimited and click Next, then click Next again without selecting any delimiters. On the final screen, leave General selected under Column data format and click Finish. Excel reinterprets eligible values as numbers. Check the results before continuing: Text to Columns can also interpret date-like text as dates, and it can change the selected data. If the wizard shows a preview, confirm that the columns will not be split unexpectedly.
- Use a formula when you want to keep the original values. Suppose A2 contains the text value 125 and you want a converted result in B2. In B2, enter
=VALUE(A2). The formula returns the numeric value 125. Fill the formula down for the other rows by dragging the fill handle or copying it into the corresponding cells. After checking the results, you can copy the converted cells and use Home > Paste > Paste Values if you want fixed numbers instead of formulas. Keep the original column until you have confirmed that the converted values are correct. - Try Paste Special for a clean range of numeric text. In an empty cell, type 1 and copy it. Select only the cells containing numbers stored as text, then choose Home > Paste dropdown > Paste Special. In the dialog, select Multiply and click OK. Multiplying eligible text-numbers by 1 converts them to numeric values without changing their magnitude. This is useful for a uniform range, but avoid it when the selection includes labels, blanks you need to preserve, formulas, or identifiers. Test a few cells or work on a copy first.
- Verify the conversion with a calculation. Check a few converted cells by using them in a total or another appropriate calculation. For example, if the converted values are in A2:A5 and contain 10, 20, 30, and 40, enter
=SUM(A2:A5)in an empty cell; the result should be 100. If the result is not what you expect, inspect the original entries for symbols, spaces, or separators before converting the entire dataset. You can also check the cell’s number format on the Home tab, but remember that a Number format by itself does not prove the stored value is numeric.
Common Mistakes
Changing the format instead of the value. Choosing Home > Number Format and selecting Number or General changes how a value is displayed. It does not reliably turn text into a numeric value. If a text value remains text, it may still be ignored by a sum or cause a lookup mismatch. Use a conversion method first, then set the display format if needed.
Removing meaningful leading zeros. Converting 00418 to a number produces 418. That is correct for a quantity, but wrong for a code whose zeros are part of its identity. Keep such values as text, or use a numeric value with a custom display format only if the data is genuinely numeric and you know the required width. Postal codes, phone numbers, and many account identifiers are best stored as text.
Converting the wrong cells. A range may contain headings, notes, blanks, or mixed data alongside numeric text. Selecting a whole column and applying Paste Special Multiply can change more than intended or produce errors. Select the actual data cells, check the selection, and use a backup when the range is important.
Assuming every number-looking string is convertible. A value can contain a currency symbol, a nonbreaking space, a thousands separator, or a decimal separator that Excel does not recognize under your regional settings. A value such as 1.234,56 may represent a different number depending on the locale. Review the original format and regional conventions before replacing separators or deleting characters.
Overlooking dates and long digit strings. Text to Columns with General can interpret date-like entries as dates. Also, Excel keeps only 15 significant digits of numeric precision; long tracking or account numbers can lose digits if converted to numbers. Store identifiers and exact long digit strings as text rather than numeric values.
Tips
Convert a small sample before changing thousands of rows. Compare the result with the source, including values with decimals, negative signs, and separators. Once the sample is correct, apply the same method to the remaining data. If you received the values from a CSV or another system, consider correcting the import settings or source format so the same issue does not return next time.
Keep raw imported data in its own column or worksheet. Add a converted column beside it, check the results, and only replace the source after confirming the conversion. This makes it easier to find an entry that failed and to recover the original text. If you use the VALUE formula, remember that its results update when the source changes; paste values only when you want to make the converted results static.
For a one-off cell, the warning menu is usually quickest. For a consistent block of imported numbers, Text to Columns can convert many cells at once. A formula is useful when you want to preserve the source or need to handle rows individually. Paste Special Multiply is convenient for a clean, purely numeric-text range, but it is less suitable for mixed data. Choose the method that best matches the content, not just the size of the selection.
If you are using Excel for Mac, the same general options are available, although ribbon and dialog layouts can vary slightly by version. Excel for the web also has many of these commands, but some conversion controls may appear differently or be unavailable for a particular selection. If a menu path is not visible, use the app’s search box to find Text to Columns or Paste Special, or open the workbook in the desktop app.
Troubleshooting
There is no warning icon. The cell may already be numeric, or Excel may not be flagging the text. Try selecting the range and using Data > Text to Columns with General selected. If that still does not work, use =VALUE(A2) in a helper column, where A2 is the cell containing the text number. If the formula returns an error, the value likely contains characters Excel cannot interpret as part of a number.
VALUE returns an error. Check the original cell for spaces before or after the digits, nonbreaking spaces copied from a website, currency symbols, or punctuation that differs from your Excel regional settings. Remove only characters that are not meaningful, then retry. For example, do not delete a comma or period until you know whether it is a thousands separator or a decimal mark. If the source is inconsistent, clean a copy of the data first and compare several results against the original.
The total still ignores the converted cells. Check that you converted the actual cells being summed, not a neighboring column, and look for remaining text entries in the range. Also inspect the formula range to make sure it includes the converted rows. A value can look numeric while containing a space or apostrophe. Try the conversion on one problem cell, then verify it with a calculation before applying the fix to the rest.
The result changes to a date or displays unexpectedly. Text to Columns may interpret a date-like string as a date, while a numeric format may show a value with decimals or as a serial date. Undo immediately with Ctrl+Z on Windows if the conversion altered data unexpectedly, then restore from the original copy if necessary. On Mac, use Command+Z. Select the appropriate number format only after confirming the underlying value and the intended display.
Paste Special Multiply does not work for every cell. Confirm that you copied a cell containing the number 1 and chose Multiply in the Paste Special dialog. If the selection includes text labels or malformed values, some cells may remain unchanged or produce errors. Undo the operation, narrow the selection to numeric text only, and test again on a small range.
Frequently Asked Questions
Does changing a cell to Number convert text into a number?
Usually not. The Number Format control changes display formatting. Use Convert to Number, Text to Columns, Paste Special Multiply, or a VALUE formula to convert text-stored digits.
How can I convert a whole column at once?
Select just the data cells in the column and use Data > Text to Columns, then keep General selected and click Finish. Check for headings, identifiers, and date-like values before converting.
Will converting text to a number remove leading zeros?
Yes. A numeric value does not retain leading zeros as part of its value, so 00418 becomes 418. Keep codes and other identifiers as text when those zeros must remain.
Why does Excel show a green triangle in the cell?
It may be Excel’s error-checking indicator for a number stored as text. Select the cell and open the warning menu to see whether Convert to Number is available. The indicator can flag other issues too, so check the menu’s explanation.
Can I convert numbers stored as text without changing the original column?
Yes. Enter =VALUE(A2) in a neighboring column when A2 contains the text number. Fill the formula down and check the results. Keep the formulas or copy and paste values into another location if you need fixed numeric results.
Should phone numbers or account numbers be converted?
Usually not. They identify something rather than represent a quantity, and may contain leading zeros or more digits than Excel can preserve accurately. Store them as text unless you have a specific reason to perform numeric calculations on them.
Related
- How to Center Text in Excel Using Keyboard Shortcuts
- Center Text in Excel Without Merging Cells
- Center Text in Excel: Keyboard Shortcut and How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Double Space Each Line in Microsoft Word EasilyTo double-space text in Word, select the text, then choose Home > Line and Paragraph Spacing > 2.0.…
- How to Find the Text Import Wizard in ExcelIn Excel for Microsoft 365 on Windows, enable the legacy Text Import Wizard at File > Options >…
- How to Sum a Row in Google Sheets: Simple FormulaTo sum a row in Google Sheets, put the total in an empty cell and enter =SUM(B2:F2). This…
- How to Compare Two Word Documents in Microsoft WordTo compare two Word documents, open the desktop app and choose Review > Compare > Compare on Windows.…
- Google Sheets Data Validation: Create Drop-Downs and RulesTo add data validation in Google Sheets, select the cells, choose Data > Data validation, then add a…
- Oracle SQL Text to Columns: Split Delimited DataTo split delimited text into separate columns in Oracle SQL, use INSTR to find delimiter positions and SUBSTR…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.