How to mass convert numbers stored as text to numbers in Excel?
Numbers imported from other systems or files are sometimes stored as text in Excel. Although they may look like ordinary numbers, they can cause calculation errors, incorrect sorting, or formulas that ignore them. This guide introduces several ways to convert a large number of text-formatted numbers into true numeric values.
- Convert a contiguous range with Convert to Number
- Convert a selected range with Paste Special
- Convert one or multiple ranges with Kutools for Excel
- Convert text to numbers with the VALUE function
- Convert text to numbers with VBA
- Frequently asked questions
Comparison of methods for converting numbers stored as text
| Method | Changes original cells | Best for | Key consideration |
|---|---|---|---|
| Convert to Number | Yes | A contiguous range displaying Excel's error indicator | The warning button must be available. |
| Paste Special | Yes | A single selected range when the warning button is unavailable | Uses a temporary cell containing 1. |
| Kutools for Excel | Yes | One range or multiple non-contiguous ranges | Requires Kutools for Excel. |
| VALUE function | No | Keeping the source data and returning converted results separately | Requires a helper column and formulas. |
| VBA | Yes | Repeatable conversions controlled by a macro | Requires a macro-enabled workbook. |
Convert a contiguous range with Convert to Number
When the text-formatted numbers are next to one another and display green error indicators, Excel's Convert to Number command provides the quickest built-in solution.
- Select the contiguous range containing the numbers stored as text. Click the warning button
that appears beside the selection. 
- Select Convert to Number from the menu. Excel converts all selected text-formatted numbers into numeric values.
that appears beside the selection. 
📝 Note:
If the warning button does not appear, Excel may not recognize the entries as numbers stored as text, or background error checking may be disabled. Try one of the following methods instead.
Easily convert text to numbers or numbers to text in Excel
Kutools for Excel's Convert between Text and Number feature converts text to numbers or numbers to text in a selected range, as shown below. Download and try it now! (30-day free trial)
Convert a selected range with Paste Special
Multiplying text-formatted numbers by 1 forces Excel to treat them as numeric values without changing their amounts. This method is useful for a single selected range when the Convert to Number command is unavailable.
- Enter 1 in a blank cell, and press Ctrl + C to copy it.
- Select the range containing the numbers stored as text. Press Ctrl + Alt + V to open the Paste Special dialog box.
- Under Operation, select Multiply, and click OK.
- Delete the temporary cell containing 1 if you no longer need it.
📝 Notes:
- This method works only when the cell contents can be interpreted as numeric text. Entries containing non-numeric characters must be cleaned first.
- Process separate areas one at a time. For multiple non-contiguous ranges, use the Kutools or VBA method below.
Convert one or multiple ranges with Kutools for Excel
The Convert between Text and Number feature in Kutools for Excel can convert numbers stored as text in a contiguous range or multiple selected ranges without formulas or a temporary helper cell.
- Select the range or multiple ranges you want to convert, and click Kutools > Content > Convert between Text and Number.
- In the Convert between Text and Number dialog box, select Text to number, and click OK.
The selected text-formatted numbers are converted directly into numeric values.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Convert text to numbers with the VALUE function
The VALUE function returns converted numbers in a separate column, so the original data remains unchanged. It is useful when the source data may be updated and you want the converted results to update with it.
- Suppose the text-formatted numbers begin in cell A1. Select a blank cell, such as B1, and enter the following formula:
=VALUE(A1) - Press Enter, and drag the fill handle down to apply the formula to the remaining rows.
- To replace the original data, copy the formula results and paste them over the original cells as values.
📝 Notes:
- If a cell contains text that Excel cannot interpret as a number, the formula returns a #VALUE! error.
- Extra spaces or other hidden characters may need to be removed before conversion.
Convert text to numbers with VBA
For recurring conversions, the following VBA macro lets you select a range and converts any text value that Excel recognizes as numeric.
- Click Developer > Visual Basic. In the Visual Basic editor, click Insert > Module, and paste the following code into the module window:
Sub ConvertTextNumbersToNumbers() Dim rng As Range Dim WorkRng As Range Dim xTitleId As String On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox( _ "Please select the range to convert text to numbers", _ xTitleId, WorkRng.Address, Type:=8) On Error GoTo 0 If WorkRng Is Nothing Then Exit Sub For Each rng In WorkRng If VarType(rng.Value) = vbString And IsNumeric(rng.Value) Then rng.Value = CDbl(rng.Value) End If Next rng End Sub - Click the Run button
or press F5. When prompted, select the range containing the numbers stored as text, and click OK.
or press F5. When prompted, select the range containing the numbers stored as text, and click OK.📝 Notes:
- The macro changes the selected cells directly. Back up important data before running it.
- Text that Excel does not recognize as numeric is left unchanged.
- Save the workbook as a macro-enabled .xlsm file if you want to keep the macro.
Frequently asked questions
Why does the Convert to Number warning button not appear?
Excel may not recognize the entries as numbers stored as text, or background error checking may be disabled. Use Paste Special, VALUE, Kutools, or VBA instead.
Why does VALUE return a #VALUE! error?
The source cell may contain spaces, symbols, or other characters that Excel cannot interpret as part of a number. Clean the source text before applying VALUE.
Will converting text to numbers remove leading zeros?
Yes. Numeric values do not preserve insignificant leading zeros. If the entries are identifiers such as ZIP codes or account codes, they may need to remain as text or use a custom number format.
Which method updates automatically when the source changes?
The VALUE formula recalculates when its referenced source cell changes. The other methods perform a one-time conversion.
Can Paste Special convert multiple non-contiguous ranges at once?
Paste Special is best applied to one selected area at a time. To process multiple separate ranges together, use Kutools or adapt the VBA method.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in