The tutorial shows many different ways to turn a string into a number in Excel: Convert to Number error checking option, formulas, mathematic operations, Paste Special, and more.
Sometimes values in your Excel worksheets look like numbers, but they dont add up, dont multiply and produce errors in formulas. A common reason for this is numbers formatted as text. In many cases Microsoft Excel is smart enough to convert numerical strings imported from other programs to numbers automatically. But sometimes numbers are left formatted as text causing multiple issues in your spreadsheets. This tutorial will teach you how to convert strings to “true” numbers.
Working with numbers is one of the most common tasks in Excel However, you may often encounter data that looks like numbers but is stored as text This typically happens when importing data from other sources. Before you can perform calculations on such data, you need to convert text to numbers in Excel.
Converting text to numbers is easy once you know the right steps. In this comprehensive guide, we’ll explore why you may have numbers stored as text, how to identify them, and efficient ways to change text to numbers in Excel.
Why Does Excel Store Numbers as Text?
There are a few common reasons your numeric data may come through as text in Excel
-
Importing data from another source: When bringing data into Excel from a website, CSV or external database, numbers sometimes retain the text format. This is because Excel can’t ascertain if it’s a number or text string.
-
Leading apostrophes or zeros: Entry of data with leading zeros or apostrophes is a key trigger for Excel to format cells as text. This prevents the numbers from being rounded off.
-
Formatting cells as text: Users may manually format cells containing numbers as text to preserve leading zeros. But this prevents mathematical operations.
-
Pasting data: Copy-pasting numbers from other sources can bring them in as text formatted cells.
No matter the reason, it’s vital to convert text to numbers for further data analysis and calculations.
How to Identify Numbers Stored as Text in Excel
Spotting numbers stored as text is the first step before conversion. Here are four quick ways to check if your data is text formatted:
-
Text format: Select the cell and check if the format is Text under Home > Format
-
Green triangle: Text cells show a small green triangle in the top left corner indicating an error
-
Apostrophes: Text numbers contain apostrophes before the digits which are not visible
-
Alignment: Text is left aligned by default unlike numbers which align to the right
Once you’ve identified text numbers, apply one of the conversion methods below.
Convert Text to Numbers by Changing Format
The most direct way to convert text to numbers is by applying the Number format:
- Select the text cells
- Open the Home tab and click the Number Format dropdown
- Choose Number and select a formatting option like Number, Currency, Accounting etc.
This changes the format of the selected cells from Text to Numbers. The green triangle disappears once conversion is successful.
While easy, this method has to be repeated for each cell. For large datasets, use one of the quicker options below.
Use Paste Special to Convert Text to Numbers
The Paste Special option lets you change text to numbers with a couple of clicks:
- Copy the text cells you want to convert
- Select a new blank cell
- Go to Home > Paste > Paste Special
- In the Paste Special box, pick Values under Paste and Number under Operation
- Click OK.
This pastes just the number values without text formatting. You can also selectMultiply or Divide under Operation to perform calculations while pasting.
Leverage the VALUE Function
Another fast way is to use Excel’s VALUE function to convert text strings to numbers:
- Select a blank cell and type =VALUE(
- Click on the text cell to be converted
- Enter ) and press Enter
The VALUE function ignores any formatting and converts text to numbers easily. You can fill or drag this formula down to convert entire columns quickly.
Use Flash Fill to Convert Text to Columns
If your data contains text and numbers combined in cells, use Flash Fill to extract just the numbers:
- Select the column containing text and numbers
- On the Data tab, click Flash Fill
- In the preview, delete the text strings leaving only the extracted numbers
- Click Flash Fill to fill the converted numbers down the column
Flash Fill uses pattern recognition to identify numbers neatly and convert text to columns in a couple of clicks.
Fix Number Stored as Text Error Using Error Checking
When a cell contains text that looks like a number, Excel flags it with an Error value warning. Use Error Checking to convert such text to numbers:
- Select the text cells showing the Error value icon
- Go to Formula > Error Checking > Convert to Number
- Click Yes when prompted to convert the error values to numbers
This automates number conversion without needing manual formatting.
When Text Numbers Don’t Convert to Numbers
In some cases, you may find certain text strings stubbornly resisting conversion to numbers in Excel. Common causes include:
-
Non-numeric characters: Text cells containing alpha characters or special symbols can’t be changed to numbers directly. You need to remove these first.
-
Leading apostrophes: Missing apostrophes entered before text numbers may stop conversion. Re-type the cell contents including the apostrophe.
-
Wrong format: Ensure Number or General format is applied and not Text which will block conversions.
-
Corrupted files: Severely corrupted Excel files may prohibit text to number conversion unless the file is repaired.
-
Formula conflicts: Names, external references or formulas applied on text data can also prevent conversion. Remove these first.
With a little attention to these aspects, you should be able to convert text to numbers successfully in Excel.
Convert Text to Numbers in Bulk Automatically
Manually converting hundreds or thousands of text entries to numbers can be extremely tedious. Here are two ways to automate conversion in bulk:
Use Power Query
Excel’s Power Query add-in has excellent data transformation capabilities:
- Select the text number cells
- Go to Data > Get & Transform Data > From Table/Range
- In the Power Query editor, select the text number column
- Go to Transform > Data Type > Whole Number
- Close and load the query to complete bulk conversion
Use VBA Macros to Convert
Power users can create a VBA macro to automate text to number conversion:
Sub TextToNumber() Dim rng As Range Set rng = Application.Selection rng.NumberFormat = "General"End Sub This macro converts all selected text cells to numbers in one click when assigned to a Quick Access Toolbar button.
Convert Numbers Stored as Text for Faster Insights
Dealing with data stuck in text formatting can be frustrating. But a little knowledge of Excel’s conversion options helps you transform numbers locked in text format back to values instantly.
Whether you need to convert a few cells manually or thousands of entries in bulk, these methods ensure your data is ready for quantitative analysis. Spend less time cleaning and more time unlocking actionable insights from your data with Excel’s powerful conversion capabilities!
How to identify numbers formatted as text in Excel
Excel has an inbuilt error checking feature that alerts you about possible problems with cell values. This appears as a small green triangle in the top left corner of a cell. Selecting a cell with an error indicator displays a caution sign with the yellow exclamation point (please see the screenshot below). Put the mouse pointer over the sign, and Excel will inform you about the potential issue: The number in this cell is formatted as text or preceded by an apostrophe. 
In some cases, an error indicator does not show up for numbers formatted as text. But there are other visual indicators of text-numbers:
| Numbers | Strings (text values) |
|
|
In the below, you can see the text representations of numbers on the right and actual numbers on the left: 
Convert to number in Excel with error checking
If your cells display an error indicator (green triangle in the top left corner), converting text strings to numbers is a two-click thing:
- Select all the cells containing numbers formatted as text.
- Click the warning sign and select Convert to Number.
Done! 
How to Convert Text to Numbers in Excel
How do I convert text strings to numbers in Excel?
If your cells display an error indicator (green triangle in the top left corner), converting text strings to numbers is a two-click thing: Select all the cells containing numbers formatted as text. Click the warning sign and select Convert to Number. Done! Another quick way to convert numerical values formatted as text to numbers is this:
How to convert a cell to a number in Excel?
For this, either select a cell and press Ctrl + C or right-click and choose Copy from the context menu. Select the cells you want to convert to numbers, right-click, and then click Paste Special. Alternatively, press the Ctrl + Alt + V shortcut. In the Paste Special dialog box, select Values in the Paste section and Add in the Operation section.
How to convert text to column in Excel?
Here we have some numbers stored as text in Excel. Step 1) Select the cells containing text-formatted numbers. Step 2) Go to the Data tab > Data Tools > Text to Column button. Step 3) In the Convert Text to Column Wizard, select Delimited. Step 4) Press the Next button. Step 5) Select Tab as the Delimiter and click on Next.
How do I convert a text to a value in Excel?
Select Values under the Paste options and select Multiply under the Operation options. Press the OK button. This will multiply all the values by 1 which was the value in the copied cell. As a result, the text is converted into values, but since you are multiplying by 1 the numbers won’t change.
