Convert Text to Numbers in Excel in Bulk

To convert text-stored values to numbers in bulk in Excel for Microsoft 365 on Windows, select the cells and choose the warning icon’s Convert to Number option if it appears. If it does not, use Data > Text to Columns and finish the wizard, or multiply the selected values by 1. Check the results before using them in calculations: the goal is to change values that look like numbers but are stored as text into actual numeric values.

This is useful after importing a CSV, copying data from a website, or receiving a report that Excel will not sum or sort numerically. Before starting, save a copy of important data and check whether the cells are numbers that should remain text, such as product codes, postal codes, or long account IDs. Excel can remove leading zeros or change very long digit strings when they become numbers.

What You’ll Need

An Excel workbook containing the values to convert. The steps below are for Microsoft Excel for Microsoft 365 on Windows.
A clear idea of which cells should be numeric, and whether the range contains headings, formulas, blanks, or codes that must keep their formatting.
For formula-based conversion, an empty helper column beside the source data. This lets you compare the converted results with the originals before replacing anything.

Step-by-Step

Identify the text-stored values. Click a few cells in the range and look for signs that numbers are stored as text. Excel may show a small green triangle in a cell’s upper-left corner, and the value may be left-aligned by default. Alignment alone is not proof, since formatting can change it. A practical check is to try a calculation on a small sample: if a value is ignored by SUM or causes a calculation problem, it may be text. Confirm that the cells contain values meant for arithmetic, not identifiers.
Select only the data you want to convert. Drag across the range, or click its first cell and use Shift-click on the last cell. For a continuous column, you can click its first data cell, then press Ctrl+Shift+Down on Windows to extend the selection to the end of a contiguous block. If there are blank rows in the data, inspect the selection because the shortcut may stop at a blank. Leave the header out. If the column mixes numeric text with labels or codes, select only the numeric-text cells rather than applying a conversion to the entire column.
Use Convert to Number when Excel offers it. With the relevant cells selected, click the warning icon beside a cell showing the green-triangle error indicator. Choose Convert to Number. Excel converts the selected flagged values, but check the selection afterward: depending on how the range is selected, the command may apply to only the affected cells or the relevant selected cells. If the warning menu does not offer this choice, or only some values change, use one of the other methods below.
Convert a clean column with Text to Columns. Select the cells containing the values, excluding the heading. Go to Data > Text to Columns. In the wizard, choose Delimited and click Next. Leave the delimiter boxes unchecked unless the data genuinely uses delimiters, then click Next. In the preview, select General as the column data format. Check that the preview displays the values as intended. If the wizard shows a Destination box, choose a blank destination if you need to preserve the original column for comparison; otherwise, make sure the selected range is the intended output area. Click Finish. This method often converts text-formatted numbers in one operation without a helper formula.
Alternatively, multiply the values by 1. In an unused cell, type 1 and copy that cell. Select the text-number range, then choose Home > Paste > Paste Special. In the dialog, choose Multiply under Operation and click OK. Multiplication by one leaves the numeric value unchanged but coerces number-like text into numbers. Delete the temporary cell containing 1 when you have checked the result. Do not use this on a range containing text labels, formulas you need to preserve, or identifiers where numeric conversion would alter the data.
Use a helper formula when you want a reviewable result. With text numbers in A2:A20 and an empty helper column starting in B2, enter =VALUE(A2) in B2. If A2 contains the text 125, the formula returns the number 125. Fill the formula down through the corresponding rows by dragging the fill handle or double-clicking it. Review the results and any errors. To replace the originals after checking, copy the converted helper cells, select the original destination, and use Home > Paste > Paste Values. Keep a backup or retain the original column until you know the conversion is correct.
Verify that the converted cells behave as numbers. Try summing a few known values, sort a copy of the range from smallest to largest, or change the cells’ number format to Number. Formatting by itself does not convert text into numbers, so use it only as a visual check. Compare a sample total with a hand-checked calculation, and inspect decimal places, negative values, and any leading zeros that may have disappeared.

For example, if A2:A4 contain the text values 12, 8, and 5, converting them should make a sum return 25. If a total remains 0 or is smaller than expected, some cells may still be text, blank, or outside the selected range. Recheck the whole column rather than assuming that one successful conversion fixed every entry.

Common Mistakes

Converting codes that are not quantities. A postal code such as 02110 is usually an identifier, not a number to add. Converting it can remove the first zero. Likewise, Excel only preserves 15 significant digits in numeric values, so long account numbers or identifiers may be altered. Keep these entries as text; if needed, apply a text format before importing or entering them rather than converting them to numbers afterward.

Choosing the wrong cells. Including a header, notes, or unrelated values in a bulk conversion can create errors or unexpected results. Selecting an entire worksheet column can also affect future entries or make it harder to see what was changed. Select the actual data range and keep a copy of the original when the source is important.

Assuming number formatting fixes the underlying value. Choosing Home > Number Format and selecting Number changes how a value is displayed, not necessarily whether a text string is stored as a number. Convert the value first, then set its display format if you want a particular number of decimal places or a thousands separator.

Overlooking spaces and unusual characters. A value that appears to be 125 may contain a leading space, a nonbreaking space copied from a web page, or a currency symbol. Excel may not convert it with the simple methods above. Do not remove characters blindly: confirm which characters are unwanted, since commas, decimal points, minus signs, and currency symbols can affect the meaning of a value.

Overwriting useful source data. Text to Columns and Paste Special can change cells directly. If the data is hard to replace or came from an external system, duplicate the worksheet or use a separate destination column first. A quick backup is safer than trying to reconstruct the original after a mistaken conversion.

Tips

Work on a representative sample before converting a large imported dataset. Include examples with decimals, negative numbers, blanks, and any unusual formatting so you can see how Excel handles them. If the sample looks right, apply the same method to the rest of the matching values.

Keep source and converted data side by side until you have checked the result. This is especially helpful for CSV imports where the source may contain leading zeros, date-like strings, or values with more digits than Excel can store precisely. If preserving the exact original characters matters, keep that column as text even if a separate numeric version is useful for calculations.

For repeated imports, consider correcting the data during import rather than fixing it each time. In Excel for Microsoft 365 on Windows, use Data > From Text/CSV to preview a file and review how its columns are interpreted before loading it. Import options and preview controls can vary by file and Excel update, so check the displayed column types and sample values. Do not assume that a column identified as a number will preserve codes or very long digit strings exactly.

Choose the method that fits the data. Convert to Number is convenient for flagged cells; Text to Columns works well on a consistent range; Paste Special with Multiply is a quick in-place option; and VALUE is useful when you want a visible, reviewable conversion. None of these methods can determine whether a number-like string is a quantity or an identifier. That decision comes from the meaning of the data.

On Excel for Mac, the menu names and wizard are generally similar, but ribbon placement and keyboard shortcuts can differ. Use the visible Data and Home menus if a Windows shortcut does not work. In Excel for the web, some desktop ribbon options and dialogs may differ; if Text to Columns or Paste Special is not available in the expected form, open the workbook in the desktop app or use a helper formula and then verify the result there.

Troubleshooting

Convert to Number is missing. The cells may not have Excel’s green-triangle warning, or the error-checking option may be disabled. Use Data > Text to Columns, Paste Special with Multiply, or the VALUE helper formula instead. If only a few cells show warnings, selecting the full range does not guarantee that every unflagged text value is converted; check the whole result.

VALUE returns an error. The text may include extra spaces, nonbreaking spaces, a currency mark, or separators that do not match your regional settings. Click into a problem cell and inspect the actual contents. Remove only confirmed unwanted characters, or clean the data in a separate column before conversion. Check whether the source uses a comma or period as its decimal separator; a value formatted for another locale may need adjustment before Excel can interpret it correctly.

Text to Columns splits the data unexpectedly. Reopen the wizard and review the delimiter choices and preview. For a simple number column, choose Delimited but leave the delimiter boxes unchecked. If the preview shows multiple columns, cancel and start again with the correct settings. Be cautious about the destination: splitting or writing over adjacent cells can replace existing content.

Some values convert and others do not. The range may contain mixed formats, hidden characters, error values, blanks, or numbers written with different separators. Sort or filter the column to group unusual entries, then examine a few of the values that failed. Convert consistent groups separately, and preserve original data while investigating rather than forcing every cell through the same operation.

The result has missing zeros or changed digits. Undo immediately with Ctrl+Z on Windows if the conversion just happened, then restore from a backup if needed. Excel cannot reliably represent leading zeros as part of a numeric value, and numbers longer than 15 significant digits lose precision. Store codes and long identifiers as text; use a custom display format only when the underlying value is short enough and the zeros are purely presentational.

Frequently Asked Questions

How do I convert an entire column of text to numbers at once?
Select the data cells, not the heading, then use the warning menu’s Convert to Number option if available. For a consistent column, Data > Text to Columns with General selected is another bulk method. Check the results, especially if the column contains mixed content.

Why does Excel treat numbers as text?
Common causes include importing a CSV, copying from a website, entering an apostrophe before a value, or receiving data with spaces or separators Excel does not recognize. The text can look exactly like a number while remaining unavailable to numeric calculations.

Can I convert text to numbers without changing the original column?
Yes. Put =VALUE(A2) in a helper column when the source value is in A2, fill it down, and review the outputs. After checking, keep both columns or copy the results and use Paste Values in a separate destination.

Will changing the cell format to Number convert text?
No. Number formatting changes the display of numeric values; it does not reliably turn text into numbers. Use a conversion method first, then apply a number format if you want to control decimal places or separators.

What is the safest method for a large or important dataset?
Make a copy, convert a sample into a separate column or destination, and compare it with the source before replacing anything. Preserve codes, postal values, and long digit strings as text. For recurring files, review the import preview and column types each time.

Can I undo a bulk conversion?
Use Ctrl+Z on Windows, or Command+Z on Mac, immediately after the change if the action is still in the undo history. If you have made further edits or saved over the only copy, undo may not be available, so keep a backup before changing important 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.