To convert text-formatted numbers in Excel, select the cells and choose the warning icon’s Convert to Number option, or use Data > Text to Columns > Finish. For a formula-based conversion, put =VALUE(A2) in another cell when A2 contains a number stored as text; then copy the results and paste them as values if you need to replace the originals.
What “number stored as text” means
A cell can display digits while Excel treats its contents as text. This often happens when data is imported from a website, copied from a report, or opened from a CSV file. The value may look like 125, but Excel stores it as characters rather than as a numeric value. That distinction matters because text may not behave as expected in sums, averages, sorting, charts, or comparisons.
Excel commonly flags these cells with a small green triangle in the top-left corner. Selecting a flagged cell may show a warning button with the option to convert it. A green triangle is a useful clue, not a guarantee: error-checking settings can be changed, and text may be intentional. A number kept as text can be appropriate for an account code, postal code, product ID, or any value where the digits are an identifier rather than a quantity.
One visual detail causes confusion: changing a cell’s number format to General or Number does not, by itself, convert text into a number. Formatting controls how a value is displayed; it does not necessarily change the underlying type. Use a conversion method, then apply the display format you want.
Choose the conversion method
The best approach depends on how many cells you have, whether the values use unusual separators, and whether you must preserve the original data. For a few ordinary values, use Excel’s warning menu. For a column imported as text, Text to Columns or Paste Special can convert many cells at once. A formula is useful when you want a separate, auditable result column or need to clean the text as part of conversion.
- A few flagged cells: select the cells, open the warning menu, and choose Convert to Number.
- A whole column of simple numeric text: try Data > Text to Columns and finish the wizard without splitting the column.
- Values with known decimal and grouping marks: use NUMBERVALUE with those separators specified.
- Identifiers with meaningful leading zeros: keep them as text rather than converting them.
Before changing a large imported range, keep an untouched copy or duplicate the sheet. That makes it easier to compare results and recover original codes or formatting if Excel interprets something differently than intended.
Use Excel’s built-in conversion tools
For cells showing the green-triangle warning, select one cell or a range of similar cells. Click the warning button beside the selection and choose Convert to Number. If a range contains mixed content, check the results rather than assuming every cell converted. The warning option is convenient, but it may not appear when Excel does not detect an error.
For a larger column, select the text values and choose Data > Text to Columns. In the wizard, choose Delimited or Fixed width as appropriate, leave the data arranged so it will not be split into unwanted columns, and click Finish. For a single field of numeric text, the delimiter choice is usually not important if no delimiter is selected. Check the preview and destination first: Text to Columns can overwrite cells to the right if the operation splits data across columns.
Another bulk option is Paste Special with multiplication. Enter the number 1 in an unused cell and copy it. Select the text-number cells, then use Home > Paste > Paste Special and choose Multiply. Multiplying numeric text by one can coerce it into numbers without changing its numeric value. This is less suitable when the selection includes nonnumeric labels, formulas, or data you do not intend to alter, so work on a copy or a carefully selected range.
Ribbon labels and dialogs can vary slightly by Excel release. These paths describe Excel for Microsoft 365 on Windows. Excel for Mac has the Text to Columns command on the Data tab as well, though dialog layout and keyboard shortcuts can differ. In Excel for the web, available commands depend on the current feature set; if a bulk conversion option is missing, use a formula or open the workbook in the desktop app.
Convert with formulas and keep a clean result
A formula is useful when you want the converted values beside the source data. With text numbers in A2:A20 and the first result cell in B2, enter this in B2 and fill it down:
=VALUE(A2)
If A2 contains the text 125, the formula returns the numeric value 125. VALUE interprets text that Excel recognizes as a number, date, or time according to the current regional conventions. If the text contains extra characters or separators that do not match those conventions, the result may be an error or an unintended interpretation.
When the source consistently uses a decimal point and comma grouping marks, you can state those marks explicitly. With text in A2 and a period as the decimal separator and comma as the grouping separator, enter:
=NUMBERVALUE(A2,".",",")
For example, if A2 contains 1,234.50, the result is the numeric value 1234.5. NUMBERVALUE avoids relying on the computer’s usual decimal and grouping marks when you specify them. Reverse the separators if the source uses a format such as 1.234,50. Confirm the source convention before applying one formula to a whole column.
After checking the formula results, you can copy the result cells and choose Home > Paste > Paste Values to replace formulas with their current results. If you need to overwrite the source column, first copy the converted results, then paste values into the original cells. This keeps the converted values from depending on a separate source column that may later be deleted.
Check separators, spaces, and other obstacles
Text that looks numeric can include invisible characters or punctuation that prevents conversion. Common causes include leading or trailing spaces, nonbreaking spaces copied from web pages, currency symbols, parentheses for negative values, and a separator convention that differs from the computer’s regional settings. If VALUE returns an error, inspect the original text before trying a more complex formula.
For example, a nonbreaking space may look like an ordinary space but remain after a normal trim operation. If A2 contains a value with ordinary extra spaces and nonbreaking spaces, and its decimal point and comma grouping marks are known, use this formula in B2:
=NUMBERVALUE(SUBSTITUTE(TRIM(A2),CHAR(160),""),".",",")
TRIM removes extra ordinary spaces, while SUBSTITUTE removes the character represented by CHAR(160). With A2 containing 1,234.50 plus a nonbreaking space, this returns 1234.5. This cleanup is intended for a known data pattern. Do not remove punctuation indiscriminately: a comma or period may be a meaningful decimal mark, and stripping it can change the amount by a factor of 1,000.
If the column mixes formats, such as 1,234.50 and 1.234,50, do not apply a single separator rule blindly. Separate the formats first or use a reliable indicator to determine how each row should be interpreted. A conversion error is preferable to silently changing a value into the wrong number.
Dates, leading zeros, and other special cases
Dates are especially easy to misread because a string such as 03/04/2025 can mean different dates in different regions. Excel may recognize it as a date and store it as a serial number, or it may leave it as text. If you convert a date-like string, confirm whether the source means March 4 or April 3 before relying on the result. In Text to Columns, the wizard’s date option lets you specify an order such as MDY or DMY; choose the source’s actual order, not merely the format used on your computer.
Leading zeros are another important exception. Converting 00127 to a number produces 127, because leading zeros have no numeric value. That is correct for a quantity, but often wrong for a postal code, employee number, or product code. Keep identifiers as text, or, if they must be numeric internally, use a custom number format with the required fixed width for display. A custom format changes appearance, however; it does not restore information if the original zeros were already lost.
Values containing percentages, currency marks, parentheses, or units may also need deliberate handling. For example, 15% may be interpreted as 0.15, while a value with a currency symbol may depend on regional settings. Decide what the text represents and check a few converted results against the source before filling a formula through thousands of rows.
Verify the result and avoid common mistakes
After conversion, test the data rather than relying only on appearance. A number and a text string can look identical in the grid. Useful checks include applying a numeric format, trying a small SUM, sorting the column, or checking whether a formula such as ISNUMBER returns TRUE. With results in B2:B20, enter this in C2 to test the first result:
=ISNUMBER(B2)
If B2 contains the converted numeric value 125, the result is TRUE. Fill the check down if you want to inspect a whole range. A sum is another practical check: if values in B2:B4 are 10, 20, and 30, the total should be 60. A correct-looking display alone is not proof, since number formats can make different underlying values appear alike.
- Do not assume that selecting Number format converts text; use a conversion tool or formula.
- Do not remove all commas or periods without confirming what each separator means.
- Do not convert identifiers just because Excel marks them as text.
- Do not overwrite imported source data until you have checked a sample and retained a backup.
- After formula conversion, paste values if the final data should not depend on the source column.
Sorting can reveal a problem: text values may sort alphabetically, so 100 can appear before 20, while numeric values sort by magnitude. However, sorting is only a clue, not a complete test, because mixed columns can contain both text and numbers. A check with ISNUMBER or a controlled formula is more direct.
Frequently Asked Questions
Why does Excel treat a number as text?
It may have been imported from a text file, copied from a website, entered with a leading apostrophe, or brought in with spaces or incompatible separators. Excel may also preserve text deliberately, such as for codes with leading zeros.
Does changing the cell format to Number convert text?
No. Number formatting changes how numeric values are displayed, but it does not reliably change text into a numeric value. Use Convert to Number, Text to Columns, Paste Special, or a formula such as VALUE.
How do I convert an entire column at once?
Select the relevant cells and use the warning menu’s Convert to Number option if it appears, or use Data > Text to Columns and finish the wizard without splitting the field. Check for data in neighboring columns before using Text to Columns.
How do I convert text with a decimal comma?
Use NUMBERVALUE and specify the source separators. For text in A2 written as 1.234,50, use =NUMBERVALUE(A2,",","."). The result is 1234.5. Confirm that the source uses a comma for decimals and a period for grouping before filling the formula down.
How can I keep leading zeros?
Keep the value as text when it is an identifier, such as a ZIP code or item number. If you convert it to a number, Excel removes insignificant leading zeros. A custom number format can display a fixed number of zeros, but it is not a substitute for retaining text when the exact original code matters.
Related
- Select in Excel Without a Mouse: Keyboard Shortcuts
- How to Select Cells in Excel Using Keyboard Shortcuts
- Select in Excel Without Dragging: Keyboard and Name Box
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add a Cover Page to a Word Document: Steps and TipsTo add a cover page in Microsoft Word for Microsoft 365 on Windows, open the document and choose…
- Outlook Block Sender Still Going to Junk Mail?Yes. In Outlook, blocking a sender normally sends that sender’s messages to the Junk Email folder; it does…
- How to Create a Sticker Template in Word for PrintingTo create a sticker template in Microsoft Word, open Mailings > Labels > Options, select the label product…
- Excel Clear Print Area: What Dotted Lines MeanTo clear a worksheet’s print area in Excel for Microsoft 365 on Windows, select the sheet and choose…
- Google Sheets: Add a Filter to a Column and Sort RowsTo add a filter in Google Sheets, select the data range, including its header row, then choose Data…
- Excel Table Styles: How to Apply and Customize ThemAn Excel table style is a coordinated set of formatting applied to an Excel table, such as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.