To convert text values to numbers in Excel, select the cells and choose the warning icon’s Convert to Number command when it appears, or use Data > Text to Columns and finish with the column format set to General. For stubborn or mixed data, use a formula such as =VALUE(A2), then copy the results and paste them as values.
Why Excel stores numbers as text
Excel distinguishes a value’s underlying type from the way it looks on the worksheet. A cell can display 250 while holding the number 250, or display the same characters while holding text. Text that looks numeric cannot always be used properly in arithmetic, sorting, charts, or functions that expect numbers.
Text numbers often arrive through copied web pages, CSV files, accounting exports, or systems that add an apostrophe before a value. A cell may also contain spaces, nonbreaking spaces, currency symbols, or separators that do not match Excel’s regional settings. Sometimes the conversion is intentional: an identifier such as 00127 should often remain text because converting it would remove its leading zeros.
Formatting alone does not fix the underlying type. Choosing Home > Number Format > General changes how numeric values are displayed, but it does not reliably turn text into numbers. Use a conversion method first, then apply the number format you want.
Use the warning icon for a quick conversion
When Excel recognizes a text value that resembles a number, it may show a small green triangle in the cell’s upper-left corner. Select one or more affected cells, click the warning icon that appears beside the selection, and choose Convert to Number. Excel changes the selected values to numeric values in place.
This is usually the fastest method for a straightforward column of values such as 12, 35, and 48. It is less useful when Excel does not flag the cells, or when the text includes extra characters or inconsistent separators. The warning menu can also offer other choices, so read the menu rather than assuming the green triangle always means a number-conversion problem.
- Use it when the values are clearly numeric and Excel shows the warning icon.
- Check the converted results if the column includes blanks, formulas, dates, or identifiers.
- If the icon is absent, use Text to Columns, Paste Special, or a formula instead.
Convert a whole column with Text to Columns
Data > Text to Columns is a practical way to coerce a selected range of text numbers into numeric values without writing a formula. It is also useful for imported data, but the wizard can split data if you choose the wrong delimiter, so limit the selection to the intended column and review the settings before finishing.
- Select the cells to convert, preferably excluding the column heading.
- Choose Data > Text to Columns.
- In the wizard, choose Delimited and select Next. For a simple numeric column, leave the delimiter boxes clear so Excel does not split values at commas or other characters.
- Select Next, choose General as the column data format, and select Finish.
Check the output, especially if the values contain commas, decimal points, dates, or leading zeros. Text to Columns can interpret content according to Excel’s settings, and a value that looks like an identifier may become a number or date. In Excel for Microsoft 365 on Windows, the command is on the Data tab. The Mac ribbon has the corresponding Text to Columns command on its Data tab, although the layout and some dialog details can vary by release.
Use Paste Special to multiply by one
Multiplying text numbers by 1 can force Excel to coerce simple numeric text into numbers while leaving the displayed magnitude unchanged. This is handy for a large range when you want to replace the original cells rather than create a helper column.
- Type
1into an unused cell and copy it. - Select the text-number cells you want to change.
- Choose Home > Paste > Paste Special, select Multiply in the dialog, and choose OK.
- Delete the temporary cell containing 1 if it is no longer needed, then verify the converted range.
This method is not suitable for every kind of text. It will not clean arbitrary words, hidden spaces, or incompatible decimal and thousands separators. It can also change the values of formulas in the selected range because Paste Special performs an operation on the cells. Select only the intended data, and consider making a copy of the column first. On Mac, use the Paste Special command from the ribbon or context menu; Windows keyboard combinations for the dialog may differ on Mac.
Convert with a formula and keep the original data
A helper-column formula is a good choice when you want to preserve the imported text, inspect the converted output, or apply the same conversion down many rows. With the text value 1250 in cell A2, enter this formula in B2:
With the text number 1250 in A2, the formula =VALUE(A2) in B2 returns the numeric value 1250. Fill the formula down the column, then copy the results and use Home > Paste > Paste Values if you need fixed numbers rather than formulas. Keep the original column until you have checked the results.
VALUE converts text that Excel recognizes as a number, date, or time into the corresponding numeric value. Because Excel stores dates and times as serial numbers, a formula may return a date serial even though a cell formatted as a date displays a calendar date. Set the output format appropriately and make sure that the value’s meaning is what you intended.
If some rows might not convert, an error-handling formula can mark them for review instead of displaying an error. With text values in A2, put =IFERROR(VALUE(A2),"Check source") in B2. A2 containing 42 returns the number 42; a value such as forty-two returns the text Check source. Review flagged rows rather than treating them as converted numbers.
Handle decimal and thousands separators
A frequent reason conversion fails is that the text uses separators from a different regional convention. For example, one source may write one thousand two hundred thirty-four and a half as 1,234.5, while another writes it as 1.234,5. Excel’s interpretation can depend on the system and workbook settings. Do not replace punctuation blindly: a comma may mean a decimal mark in one dataset and a thousands mark in another.
For a known format, NUMBERVALUE lets you specify the decimal and group separators. With A2 containing the text 1,234.50, enter =NUMBERVALUE(A2,".",",") in B2. It returns the number 1234.5: the period is the decimal separator and the comma is the group separator. This function is available in current Microsoft 365 Excel.
If the source instead contains 1.234,50, use the corresponding separator order. With that text in A2, =NUMBERVALUE(A2,",",".") returns 1234.5. Confirm the source convention before applying a formula to an entire column. A mistaken separator choice can produce an error or a plausible but incorrect value.
Dates, currency, spaces, and leading zeros
Not every string made of digits should become a number. Product codes, postal codes, account numbers, and phone numbers are usually identifiers, not quantities. Converting them can strip leading zeros, and arithmetic on them is generally not meaningful. Keep these values as text, or preserve a fixed-width display using a suitable custom number format only when the underlying value is truly numeric and that approach fits the task.
Dates deserve special care. Excel stores real dates as numbers and displays them according to a date format. A text date may convert successfully but be interpreted differently depending on its order, such as month-day-year versus day-month-year. Verify ambiguous dates against the source rather than assuming that a successful conversion proves the date is correct.
Currency symbols and spaces can also stop a direct conversion. A normal space may be visible, while a nonbreaking space copied from a web page looks nearly identical but behaves differently. If a formula returns an error, inspect the source and clean only the unwanted characters. Avoid removing every comma, period, or symbol indiscriminately: those characters may carry essential meaning or distinguish decimal values.
Choose a method and check the result
The best method depends on whether you need a quick, one-time fix or a repeatable transformation. A warning-icon conversion is fastest for clean, recognized values. Text to Columns works well for a selected column. Paste Special is convenient for simple numeric text that can be multiplied by one. A formula is more transparent when you need to preserve the source, handle exceptions, or specify separators. For recurring imports, Power Query can make the conversion part of a refreshable process.
After conversion, verify more than the cell’s appearance. Try a simple calculation in an unused cell, such as adding a known numeric value to one converted cell, or sort a small sample numerically and check that the order makes sense. A numeric value generally aligns to the right by default, but alignment is not conclusive because formatting can override it.
For repeated cleanup, import or convert the range in Power Query: select the data, choose Data > From Table/Range, then in the Power Query Editor select the column and choose Transform > Data Type > Decimal Number (or another suitable type). Review any conversion errors, then choose Home > Close & Load. Power Query is useful when the same source needs refreshing, but its type choice should still match the data and locale. Menu names can vary slightly between Excel updates.
Frequently Asked Questions
Why does Excel still treat a number as text?
Common causes include a leading apostrophe, spaces, nonbreaking spaces, currency symbols, or separators Excel does not recognize under the current regional settings. Check a few raw values and use Text to Columns, a suitable formula, or a controlled cleanup step.
Does changing the cell format to Number convert text?
No. Number formatting changes the display of numeric values; it does not reliably change text into numeric values. Convert the content first, then apply a number format if needed.
How do I convert text numbers without losing leading zeros?
Do not convert identifiers such as 00127 if the zeros are meaningful. Keep them as text. If they represent quantities instead, conversion is appropriate, but the zeros will not remain part of the numeric value unless you apply a display format designed for that purpose.
Why does VALUE return an error?
The text may contain characters or separators Excel cannot interpret, or it may not represent a number at all. Check for hidden spaces and confirm the decimal and thousands conventions. Use NUMBERVALUE when you know the separators used in the source.
Can I convert text dates to real Excel dates?
Often, yes, using Text to Columns or a formula, but verify the date order and resulting values. Excel stores real dates as serial numbers and displays them using a date format; an ambiguous text date can convert to the wrong day or month without an obvious warning.
Related
- How to Ungroup in Excel: Sheets, Rows, and Columns
- Superscript in Excel: How to Format Text and Numbers
- Share an Excel Link: Steps, Access, and Safety
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Not Responding When Deleting Rows? Causes and FixesIf Excel stops responding while deleting rows, first give it a little time: removing rows can trigger lengthy…
- Insert Checkbox in Excel Without the Developer TabYes. In current versions of Excel for Microsoft 365, select the cell or range where you want checkboxes,…
- Excel Shortcut to Insert a Row and Shift Cells DownIn Excel for Microsoft 365 on Windows, select the cell where the new space should begin and press…
- Excel AutoFilter Dates Before TodayTo show dates earlier than today in Excel, select your data and choose Data > Filter, then open…
- How to Alphabetize Tabs in Google SheetsGoogle Sheets does not have a built-in command to alphabetize sheet tabs. For a few tabs, drag them…
- Microsoft Excel Compare Two Sheets: Methods That WorkTo compare two Excel sheets, use a formula when matching cells occupy the same positions, or compare records…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.