Convert Text to Numbers in Excel: Methods That Work

How-to basics · Updated November 22, 2026

To convert text numbers in Excel, select the cells and choose the warning icon’s Convert to Number command when it appears. If there is no warning icon, use Data > Text to Columns and finish the wizard, or convert a value with a formula such as =VALUE(A2). The right method depends on whether you need to fix a range in place, preserve the original text, or account for regional number formats.

Why numbers are stored as text

Excel distinguishes a number from a string of characters that looks like one. A numeric value can be added, sorted numerically, and used in arithmetic. A text value such as 125 may look identical in a cell, but Excel may sort it alongside words or leave it out of calculations. Text numbers often appear left-aligned by default, while numbers are usually right-aligned, although alignment can be changed manually and is not a definitive test.

Common sources include data copied from a website or PDF, CSV imports, accounting or inventory exports, and values entered with a leading apostrophe. A cell formatted as Text before data is entered can also cause later entries to remain text. Spaces that are hard to see, a nonbreaking space, or punctuation from another country’s number format may prevent a conversion.

Check a few affected cells before converting a whole column. If the values are product codes, postal codes, or account IDs, they may be identifiers rather than quantities. Converting an identifier to a number can remove leading zeros, so a successful conversion is not always the right outcome.

Use Convert to Number for a quick repair

When Excel recognizes a text number, it may show a small green triangle in the cell’s upper-left corner. Select the affected cell or range, click the warning icon that appears, and choose Convert to Number. This changes the stored values in place, so it is a convenient option for a clean column of ordinary numbers.

  1. Select one flagged cell or drag across the contiguous range you want to repair.
  2. Click the warning icon beside the selection.
  3. Choose Convert to Number, then check the results in a few cells.

The warning does not appear for every text value, and Excel may not flag values with unusual characters or inconsistent formatting. If the command is missing, use Text to Columns or a formula instead. The warning menu can also offer other choices, such as ignoring the error; do not choose that if the goal is to make the cells numeric.

Convert a column with Text to Columns

Text to Columns is a useful built-in method when the numbers are already in a column and you want to replace the text values. In Excel for Microsoft 365 on Windows, select the cells and go to Data > Text to Columns. In the wizard, choose Delimited, click Next, clear any delimiters that could split the values, and click Next. Set the column data format to General, choose a destination if you want to keep the original cells, and click Finish.

For a column containing only values such as 125 or 42.75, the default tab delimiter usually does not split anything, but check the preview before finishing. If the data includes commas, tabs, or other delimiter characters, clear the matching delimiter or the wizard may divide a value into separate columns. Choosing General allows Excel to interpret ordinary numeric text; choosing Text deliberately preserves it as text.

Text to Columns can also interpret date-like strings as dates, depending on the data and settings. If preserving the exact characters matters, make a copy of the source column first or select Text as the column format. The command is available in Excel desktop; if you do not see it in Excel for the web, use a formula or open the workbook in the desktop app. Menu placement can vary slightly between Windows and Mac, but the Data tab contains the Text to Columns command in desktop Excel.

Use a formula when you want a separate result

A formula is preferable when you want to keep the imported text unchanged, audit the conversion, or apply one rule to many rows. With the text value in A2, enter a conversion formula in an empty cell on the same row, then fill it down. After checking the results, copy the formula results and use Home > Paste > Paste Values if you want fixed numbers rather than formulas.

  • With the text number 125 in A2, enter =VALUE(A2). The result is the numeric value 125.
  • With the text number 125 in A2, you can also enter =A2*1. The result is 125; multiplication by one coerces a clean numeric string into a number.

VALUE is clearer about the intent, while multiplying by one is a compact coercion trick. Neither method can reliably repair every malformed string. If A2 contains extra text, an unsupported currency symbol, or an unrecognized separator, Excel may return an error instead of a number. Do not hide such errors before investigating them: the failure can reveal a genuine data-quality issue.

Handle decimal and thousands separators

A value such as 1,234.50 can mean different things under different regional conventions. One system uses a comma for grouping thousands and a period for decimals; another may use a period for grouping and a comma for decimals. Excel’s interpretation depends on the separators it is using, so a value imported from another locale may be rejected or misread.

Use NUMBERVALUE when you know the separators in the source text and want to specify them directly. With the text 1.234,50 in A2, where the period is the grouping separator and the comma is the decimal separator, enter =NUMBERVALUE(A2,",","."). The result is 1234.5. The second argument identifies the decimal separator; the third identifies the group separator. This avoids relying on the workbook’s current regional separators.

For a different source convention, change those arguments to match the text exactly. For instance, text written as 1,234.50 uses a period as the decimal separator and a comma as the group separator. Avoid replacing punctuation across an entire column without checking examples: a comma can be a decimal mark in one dataset and a thousands mark in another. Excel’s decimal and thousands separator settings can be adjusted through File > Options > Advanced on Windows, but that changes how Excel handles numbers more broadly, so a targeted formula is often safer for a one-off import.

Choose a method that fits the data

Before converting, decide whether you need to overwrite the source, retain a traceable original, or preserve characters exactly. A practical choice guide is:

  • Clean range with green warning triangles: use Convert to Number for the quickest in-place repair.
  • One plain-text numeric column: use Text to Columns when you have checked the preview and do not need a formula trail.
  • Repeated imports or uncertain source formatting: use VALUE or NUMBERVALUE in a helper column, then review and paste values if appropriate.
  • Identifiers with leading zeros: keep them as text; conversion is not appropriate merely because they contain digits.
  • Text with spaces or unusual characters: inspect and clean the source before choosing a conversion method.

For a one-time repair, in-place conversion is usually fastest. For a process that will be repeated, a helper column makes the transformation visible and repeatable. It also lets you compare the original and converted values before replacing anything. Keep a copy of important source data until you have verified the results.

Leading zeros, spaces, and other common problems

Leading zeros are the most important reason not to convert blindly. If a cell contains the text 00127 and represents a product code, converting it to a number produces 127 and loses information. You can preserve the code as text, or store a number and apply a suitable number format when the code has a fixed, known width. A custom display format changes how a numeric value looks; it does not restore zeros that were removed from the underlying identifier.

Whitespace can also block conversion. A normal space at the start or end of a value may be removable with TRIM, but TRIM does not remove every nonprinting character, including some nonbreaking spaces copied from web pages. Inspect a suspicious value in the formula bar and compare it with a manually entered example. If cleaning is needed, use a helper column and check the outcome rather than applying broad replacements that might remove meaningful characters.

Other frequent causes of failed conversion include currency symbols, parentheses for negatives, trailing minus signs, and text such as N/A mixed into a numeric column. Different source systems represent these values differently, so there is no universally safe automatic fix. Separate genuine numbers from labels and exceptions, then choose a rule that matches the source’s documented format.

Verify that Excel now sees numbers

Do not rely only on how a cell looks. Select a converted cell and check the formula bar, try sorting a small sample, or test it in a simple calculation. For example, a numeric value can participate in a sum; a text value may be ignored by some calculations or produce unexpected results. Be careful with a whole-column total, since blank cells and other text can make a check look successful even when some entries were not converted.

Excel’s Home > Number controls can change a cell’s display format, but formatting alone does not convert text into a number. Applying Number or Currency formatting to a text string may leave it as text. First convert the stored value; then choose a number format to control decimal places, grouping, or currency display. On Windows, Ctrl+1 opens Format Cells; on Mac, use Command+1. These shortcuts open formatting options, not a text-to-number conversion command.

Frequently Asked Questions

Why does Excel treat a number as text?
It may have been imported as text, entered while the cell was formatted as Text, prefixed with an apostrophe, or contain spaces or separators Excel does not recognize. Check the source and regional number conventions before converting.

How do I convert text to numbers without a formula?
Use the warning icon’s Convert to Number command when available, or select the range and use Data > Text to Columns. Review the wizard preview and destination before finishing.

Does changing the cell format from Text to Number convert it?
No. Number formatting changes display, not necessarily the stored value. Convert the text with a conversion command, Text to Columns, or a formula first, then apply the number format you want.

How can I convert numbers with a comma decimal separator?
Use NUMBERVALUE and specify the separators present in the source text. For example, with 1.234,50 in A2, =NUMBERVALUE(A2,",",".") returns 1234.5.

Why did my leading zeros disappear?
Excel removes leading zeros when it stores a value as a number because they do not change the numeric quantity. Keep codes and identifiers as text, or use a deliberate fixed-width number format only when that representation is appropriate.

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.