Microsoft Excel: How to Convert Text to Numbers Safely

How-to basics · Updated November 20, 2026

To convert text to numbers in Microsoft Excel, select the affected cells and choose the error-checking option Convert to Number if Excel shows a green triangle. If that option is unavailable, use Data > Text to Columns and finish with the column format set to General, or convert a cell with a formula such as =VALUE(A2). Check the result before replacing your original data, especially when leading zeros or regional number formats matter.

Why Excel sometimes treats numbers as text

A cell can display digits and still contain text. This often happens when data comes from a website, a CSV file, a reporting system, or a manually entered value with a leading apostrophe. Excel may also import a whole column as text if its entries are inconsistent. Text that looks numeric can be left-aligned by default, while numeric values are usually right-aligned, but alignment is only a clue: formatting and alignment settings can be changed.

The distinction matters because text values may not behave like numbers in calculations, sorting, charts, or functions such as SUM. For instance, a SUM formula can ignore numeric-looking text in a referenced range. Sorting can also place text values in a different order from true numbers. A cell’s number format does not change its underlying value: choosing Number on the Home tab may alter how a real number appears, but it does not reliably convert text into a numeric value.

Before converting, consider what the digits mean. An entry such as 00427 might be an ID, not a quantity. Turning it into a number changes its value to 427 and removes the zeros. Phone numbers, postal codes, product codes, and account identifiers are usually better kept as text, even though they contain only digits.

Use Excel’s Convert to Number option

For a small range, Excel’s built-in warning is often the quickest approach. In Excel for Microsoft 365 on Windows, select a cell marked with a green triangle. Click the warning icon that appears beside it, then choose Convert to Number. To convert a group, select the affected cells first and use the option for the selection if Excel offers it. The warning normally appears when Excel recognizes a value that resembles a number but is stored as text.

This option is convenient because it changes the selected values directly rather than creating a separate results column. However, a green triangle is not guaranteed to appear. Error-checking settings, the way data was imported, or the kind of text can affect whether Excel flags a cell. The wording and placement of the warning control can vary slightly in other desktop editions and on Mac. If the option is missing, use one of the methods below instead of assuming the cells are already numeric.

After conversion, test a representative cell with a calculation or inspect its value in the formula bar. Do not judge success only by appearance: changing the cell’s alignment or number format can make text look like a number without changing its data type.

Convert a column with Text to Columns

Text to Columns can coerce numeric-looking text into numbers and is useful when you want to convert a whole column in place. First make a copy of the data or insert a backup column. Select the cells to convert, open Data > Text to Columns, choose Delimited, and select Next twice without choosing a delimiter. On the final screen, leave the column data format as General, then select Finish.

General tells Excel to interpret recognizable values using its current settings. Review the results, particularly if the column includes IDs, dates, or values with leading zeros: Excel may interpret those differently from your intent. If the source column contains fields separated by commas, tabs, or another delimiter, Text to Columns can split it as well. In that case, choose the correct delimiter and preview the split before finishing so you do not accidentally overwrite neighboring data.

The command is available in Excel for Microsoft 365 on Windows and Mac, though dialog details can differ. Excel for the web may not expose the same full Text to Columns workflow as the desktop app. If you cannot find it in your version, use a conversion formula or open the workbook in the desktop application.

Use a formula when you need a reversible result

A formula is useful when you want to preserve the original text and create converted values in a new column. With the text value in A2, enter =VALUE(A2) in B2. If A2 contains the text 125, the formula returns the numeric value 125. Fill the formula down for the remaining rows, then check for errors before deciding whether to keep the formula results or replace the source column.

VALUE converts text that Excel recognizes as a number, date, or time according to the workbook’s regional conventions. If the input has an unexpected currency symbol, extra characters, or separators that conflict with those conventions, the formula may return #VALUE!. When conversion succeeds, the result is a number; set its display style separately with the Number Format controls if you want decimals, thousands separators, or currency symbols.

For text with ordinary extra spaces and nonbreaking spaces, clean the value as part of conversion. With the original text in A2, =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) replaces nonbreaking spaces with normal spaces, removes extra ordinary spaces at the ends, and converts the cleaned text. If A2 contains a nonbreaking space followed by 42, the result is numeric 42. This will not remove every possible unwanted character; inspect the source if errors remain.

When your source uses a known decimal and thousands separator, NUMBERVALUE gives you explicit control. With text in A2 written as 1,234.50, enter =NUMBERVALUE(A2,".",","). The first quoted separator is the decimal mark and the second is the group separator, so the result is 1234.5. This can be clearer than relying on the computer’s regional settings. The function is available in current Excel versions, including Microsoft 365.

Convert with Paste Special arithmetic

If you want to convert values in place without a helper column, Paste Special can apply a simple arithmetic operation. Type the number 1 in an unused cell and copy it. Select the text-number cells, open Home > Paste > Paste Special, choose Multiply under Operation, and confirm. Multiplying a numeric text value by 1 produces the same quantity as a number. For example, text 18 becomes numeric 18.

This method changes the selected cells, so keep a copy first if the original text must be retained. It can fail or produce unexpected results when entries contain unrecognized characters, mixed formats, or identifiers whose leading zeros matter. Paste Special dialogs and keyboard access differ between Windows and Mac; use the ribbon path if a shortcut does not match your version. Avoid selecting entire worksheets unnecessarily, since it is easier to review a defined range.

Handle decimal marks, thousands separators, and dates

Regional settings can explain why a value that converts on one computer fails on another. Some regions write one thousand and a half as 1,000.5; others use 1.000,5. Excel’s default interpretation follows regional settings, so a pasted value may be read differently from how the source intended it. Use NUMBERVALUE with explicit separators when you know the source convention, or choose the appropriate locale during import or transformation.

Currency symbols and percent signs need attention too. A value such as $25.00 may be recognized in a matching locale, but a symbol embedded as text can prevent a simple conversion. A percent entered as text may require interpreting the percent sign correctly; converting the text 25% should produce the numeric value 0.25, which can then be formatted as a percentage to display 25%. Always verify both the stored value and its intended meaning.

Dates are another source of confusion. Excel stores recognized dates as serial numbers and displays them through a date format. A date-looking text string may convert according to regional order, so 03/04/2025 can be ambiguous between March 4 and April 3. Confirm the source’s date convention before converting a large range. If the values are identifiers or dates that must remain exact strings, preserve them as text rather than forcing numeric conversion.

Choose a method and check the result

Choose based on the size of the range, whether you need to keep the source, and whether Excel can interpret the text consistently:

  • Convert to Number: fast for cells Excel flags, with little setup.
  • Text to Columns: practical for a selected column when General interpretation is appropriate.
  • VALUE or NUMBERVALUE: best when you want a separate result column or need explicit separator handling.
  • Paste Special Multiply: useful for an in-place conversion of clean, numeric text.
  • Power Query: helpful for repeatable imports and larger data-cleaning workflows.

For a quick validation, use a test cell with a calculation. With the converted value in B2, enter =ISNUMBER(B2); it returns TRUE if B2 contains a number and FALSE if it is still text. You can also compare a small sample with the original source, check the number of converted rows, and sort a copy of the values to see whether numeric order is sensible. A check formula verifies type, not accuracy, so it cannot tell whether a date or separator was interpreted correctly.

Use Power Query for repeatable imports

If you regularly import the same kind of file, Power Query can make conversion repeatable. In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > From Table/Range to open the Power Query Editor. Select the column, then use Transform > Data Type and choose Whole Number or Decimal Number as appropriate. Review the preview for errors, then select Home > Close & Load to return the transformed data to Excel.

When a column uses a different regional convention, use the data-type option that lets you specify a locale, where available, and choose the source’s actual locale rather than guessing. Power Query is particularly useful when you refresh an import later: the transformation steps can be applied again to refreshed data. Its menus may differ somewhat across Windows, Mac, and Excel for the web, and availability depends on the version. For a one-time handful of cells, the built-in conversion option is usually simpler.

Frequently Asked Questions

Why does Excel show a green triangle in a number cell?
It commonly indicates that a value looks like a number but is stored as text. Select the cell and use the warning icon’s Convert to Number option if appropriate. The triangle is a warning clue, not proof that every flagged value should be converted.

Does changing the cell format to Number convert text?
No. Number format controls how numeric values appear; it does not reliably change text into a numeric value. Use a conversion method such as Text to Columns, VALUE, or Paste Special, then apply a number format if you need a particular display.

How can I convert text numbers without losing leading zeros?
If the zeros are meaningful, do not convert the identifiers to numbers. Keep them as text. If you need a numeric calculation and a zero-padded display, retain a source copy and decide on an appropriate display format separately; numeric conversion itself removes leading zeros from the stored value.

Why does VALUE return #VALUE!?
The text may include spaces, nonbreaking spaces, currency characters, or separators Excel does not recognize under the current regional settings. Clean the input, check the source’s decimal and thousands marks, or use NUMBERVALUE with explicit separators. Test a few examples before filling a formula down a large dataset.

Can Excel convert text to numbers automatically when I paste data?
Sometimes Excel recognizes pasted values and converts them, but behavior depends on the source, format, and regional settings. Do not assume every pasted column has the intended data type. Validate imported values before using them in calculations or reports.

Related

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.