Convert Text to Number in Excel Cells: Quick Fixes

To convert text to numbers in Excel, select the affected cells and click the warning icon, then choose Convert to Number. If no warning appears, use Data > Text to Columns and finish with the column format set to General. You can also convert a value in a helper column with a formula, then paste the results back as values. These methods change text-stored numerals into real numbers that Excel can calculate, sort, and chart properly.

What You’ll Need

An Excel workbook with the text-formatted values you want to convert. Make a copy of the sheet or workbook first if the data is important, especially before converting a large range.
A clear idea of whether the entries are quantities that should be numeric or identifiers such as product codes, account numbers, or ZIP codes. Identifiers often need to stay as text to preserve leading zeros.
If you plan to use a formula, an empty column beside the original data where you can place the converted results.

Step-by-Step

Check that the values are stored as text. Select one affected cell and look for a small green triangle in its upper-left corner. Excel may also show a warning icon beside the selection. Text-stored numbers are often left-aligned by default, while numbers are right-aligned, but alignment can be changed, so do not rely on it alone. You can also test a cell with a simple calculation in a spare area or check whether sorting puts values such as 2, 10, and 100 in the order 10, 100, 2. That order suggests they may be text.
Use Convert to Number when Excel offers it. Select the cell or range with the green error indicator, click the warning icon that appears, and choose Convert to Number. For a contiguous range, select all affected cells before opening the warning menu; Excel will convert the selected cells when that option is available. Review a few results afterward. A converted number usually aligns according to the cell’s existing formatting, and basic arithmetic with it should work.
Convert a whole column with Text to Columns. This is useful when there is no warning icon or when you want a direct conversion without a formula. Select the cells containing the text numbers, then choose Data > Text to Columns. In the wizard, choose Delimited and click Next. Leave the delimiter boxes unchecked unless the data genuinely contains separated fields; click Next again. Under Column data format, choose General, confirm that the destination is the intended range, and click Finish. For a single column of plain numerals, this tells Excel to interpret the entries as numbers rather than text. If the selection includes several columns, check the preview and destination carefully so the wizard does not overwrite neighboring data.
Use VALUE in a helper column when you want to preserve the original. With the text value in A2 and an empty output cell in B2, enter =VALUE(A2). If A2 contains the text 1250, the formula returns the numeric value 1250. Press Enter, then use the fill handle at the lower-right corner of B2 to copy the formula down alongside the rest of the list. Check that the output cells show numbers and that the formula has adjusted to each corresponding row.
Replace formula results with fixed numbers if needed. Formulas are useful for a repeatable conversion, but they refer to the original cells. If you want standalone numbers, select the formula results, copy them, and use Home > Paste > Paste Values to replace the formulas with their calculated values. You can then remove the original text column if you no longer need it. Keep the source column until you have checked the converted data, particularly if the values contain decimals, dates, or currency symbols.
Set the display format after conversion. Converting a value and choosing how it looks are separate tasks. Select the converted cells and use Home > Number Format to choose Number, Currency, Percentage, or another suitable display. A format change alone does not convert text into numbers, so use one of the conversion methods first. For example, applying Number format to text that reads 1250 may leave it stored as text until you convert it.
Verify the result with a calculation. Test a converted value by adding it to a known number or including it in a SUM formula in a spare cell. For example, if B2 contains the converted value 1250, adding 1 should produce 1251. If the result is unchanged, is an error, or the total ignores the cell, inspect the source for spaces, unusual separators, or nonnumeric characters before repeating the conversion.

In Excel for Microsoft 365 on Windows, the ribbon paths above are available in the desktop app. In Excel for Mac, the conversion commands are generally on the Data and Home tabs as well; keyboard shortcuts differ, with Ctrl+1 opening Format Cells on Windows and Command+1 doing so on Mac. Excel for the web may present a different ribbon layout. If Text to Columns or the warning-menu conversion is not available in your web session, use a helper-column formula, or open the workbook in the desktop app.

Common Mistakes

Changing the cell format and expecting the stored value to change. Choosing Number from the number-format list changes how a numeric value is displayed; it does not necessarily reinterpret text as a number. Convert the underlying entry first, then apply a display format if you need decimal places, thousands separators, or a currency symbol.

Converting identifiers that need leading zeros. A code such as 00427 may be meaningful as a label, not a quantity. When converted to a number, it becomes 427, and the original zeros are lost. Keep codes, ZIP or postal codes, and account identifiers as text unless your task specifically requires arithmetic. If you need a numeric value but must display a fixed number of digits, convert it and apply a custom number format such as 00000, while remembering the underlying value no longer contains those zeros.

Ignoring regional decimal and thousands separators. Excel reads numbers according to your regional settings. A value written as 1,25 in one locale may mean one and a quarter, while another locale may use a period for the decimal mark. Review the Text to Columns preview and choose the correct separators when the wizard offers advanced settings. Do not remove commas or periods blindly: they may be decimal marks, thousands separators, or part of an identifier.

Overwriting useful source data. Text to Columns can write its output over the selected range, and pasting converted values over the original removes the text version. Keep a backup or use a helper column until you have checked the conversion. This is particularly important when a column mixes numbers with notes, blanks, or IDs.

Assuming every text entry is a plain number. A cell may contain a currency symbol, a percent sign, a date, a nonbreaking space, or a leading apostrophe. Some of these can be interpreted by Excel, but others prevent conversion or change the value’s meaning. Inspect representative entries before applying a method to thousands of rows.

Tips

For a clean list of plain numerals, the warning-menu option is usually fastest. Text to Columns is a practical range-level alternative, while a helper formula is best when you want to keep the originals, diagnose messy input, or repeat the process. Apply the same method to a full data column only after checking several typical cells and any unusual ones.

If ordinary spaces before or after the digits are the problem, and the text value is in A2 with the converted result going in B2, use =VALUE(TRIM(A2)). If A2 contains the text 1250 with spaces before or after it, this formula returns the number 1250. TRIM removes ordinary leading and trailing spaces and reduces repeated internal ordinary spaces to one; it does not remove every invisible character, so inspect the data if this formula still returns an error.

Convert a sample first when the data contains decimal marks, dates, percentages, or currency. A text entry that looks like 15% might be interpreted as the numeric value 0.15 and displayed as 15% when formatted as a percentage. Dates also have underlying serial numbers in Excel, so a successful conversion may display as a date or as a serial value depending on the cell format. Choose the display format that matches the intended meaning after confirming the underlying value.

For recurring imports, note the exact cleanup steps that work and use them consistently. For example, keep the raw imported column, add a conversion column, and compare a few records after every refresh. If the source changes its separator or includes new symbols, a previously reliable conversion may produce errors or incorrect values. A quick spot-check of the first, middle, and last rows can catch those changes early.

Troubleshooting

There is no warning icon. The green-triangle indicator appears only for certain detected errors and may be disabled or absent for some selections. Select the range and try Data > Text to Columns, choosing General as the column format, or use =VALUE(A2) in a helper column when the text value is in A2. The formula returns a number for text Excel recognizes as numeric; copy it down and inspect any errors individually.

VALUE returns #VALUE!. The cell may contain characters that are not part of a number, such as a currency symbol Excel does not recognize, a nonbreaking space, or a decimal separator that conflicts with regional settings. Click into the source cell and inspect the beginning and end of the text. Retype a small sample manually to see whether the issue is a hidden character or the data’s number convention. For a column with mixed formats, clean or standardize the entries before converting the entire range.

The result has the wrong decimal value. Stop and undo immediately if the conversion changed the meaning. Check whether the source uses commas or periods as decimal and thousands separators, then review the region settings and Text to Columns options. For example, a string containing 1.234 can represent one point two three four or one thousand two hundred thirty-four depending on the convention. Confirm the intended interpretation from the source system before applying a fix to the full column.

Some rows convert and others do not. The column may contain a mixture of clean numbers, blanks, labels, and values with hidden spaces. Filter or sort the helper-column results to locate errors, then inspect those source cells. Do not replace errors with zero unless zero is genuinely the correct value; a blank, unknown amount, or explanatory label has a different meaning. Handle exceptional rows separately and retain the raw data until the output is verified.

Numbers still do not add up. Confirm that the cells are numeric rather than text, check that the formula range includes the expected rows, and look for values stored as percentages, dates, or rounded display values. The displayed number may hide decimal places even when the underlying value has them. Increase the displayed decimal places temporarily through the Home tab to inspect the actual precision, and check whether the original data includes excluded rows or text entries.

Frequently Asked Questions

How can I convert text to numbers without a formula?
Use the warning icon and choose Convert to Number when Excel offers it. Otherwise, select the range and use Data > Text to Columns, choosing General as the column format. Both methods convert the selected source cells directly, so retain a copy if you need to preserve the original text.

Does changing the cell format to Number convert text?
No. Number format changes how a value is displayed but does not reliably change text storage into numeric storage. Convert the text first with the warning menu, Text to Columns, or a formula, then choose a number format if you want a particular appearance.

How do I convert an entire column?
Select the range of text numbers in the column and use Convert to Number if the warning menu supports the selection. If not, run Text to Columns on the selected range and set the column data format to General. For a formula-based approach, enter a conversion formula beside the first row, fill it down, and paste values when you are satisfied.

Can I keep leading zeros after converting?
A true number does not retain leading zeros as part of its value. If the zeros identify a code, leave the entry as text. If the value must be numeric but should appear with a fixed digit count, apply a custom number format after conversion; this displays zeros without storing them as part of the number.

Why does Excel put converted numbers on the right?
Numbers are right-aligned by default in Excel, while text is usually left-aligned. This can be a useful clue, but alignment is not a conclusive test because users can apply their own alignment settings. Verify conversion with a calculation or by checking whether Excel recognizes the cell as numeric.

Will these steps work in Excel for Mac or the web?
Excel for Mac has similar conversion and formula options, although keyboard shortcuts differ from Windows. In Excel for the web, the ribbon and available commands can vary; try the warning menu or use a helper formula if Text to Columns is unavailable. For a large or complex conversion, open the workbook in Excel for Microsoft 365 on Windows or Mac and verify the result before replacing the source data.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.