It’s common to find numbers stored as text in Excel. This leads to incorrect calculations when you use these cells in Excel functions such as SUM and AVERAGE (as these functions ignore cells that have text values in it). In such cases, you need to convert cells that contain numbers as text back to numbers.
Now before we move forward, let’s first look at a few reasons why you may end up with a workbook that has numbers stored as text.
- Using ‘ (apostrophe) before a number.
- A lot of people enter apostrophe before a number to make it text. Sometimes, it’s also the case when you download data from a database. While this makes the numbers show up without the apostrophe, it impacts the cell by forcing it to treat the numbers as text.
- Getting numbers as a result of a formula (such as LEFT, RIGHT, or MID)
- If you extract the numerical part of a text string (or even a part of a number) using the TEXT functions, the result is a number in the text format.
Now, let’s see how to tackle such cases.
Convert Text to Numbers in Excel
In this tutorial, you’ll learn how to convert text to numbers in Excel.
The method you need to use depends on how the number has been converted into text. Here are the ones that are covered in this tutorial.
- Using the ‘Convert to Number’ option.
- Change the format from Text to General/Number.
- Using Paste Special.
- Using Text to Columns.
- Using a Combination of VALUE, TRIM, and CLEAN function.
Convert Text to Numbers Using ‘Convert to Number’ Option
When an apostrophe is added to a number, it changes the number format to text format. In such cases, you’ll notice that there is a green triangle at the top left part of the cell.
In this case, you can easily convert numbers to text by following these steps:
- Select all the cells that you want to convert from text to numbers.
- Click on the yellow diamond shape icon that appears at the top right. From the menu that appears, select ‘Convert to Number’ option.
This would instantly convert all the numbers stored as text back to numbers. You would notice that the numbers get aligned to the right after the conversion (while these were aligned to the left when stored as text).
Convert Text to Numbers by Changing Cell Format
When the numbers are formatted as text, you can easily convert it back to numbers by changing the format of the cells.
Here are the steps:
- Select all the cells that you want to convert from text to numbers.
- Go to Home –> Number. In the Number Format drop-down, select General.
This would instantly change the format of the selected cells to General and the numbers would get aligned to the right. If you want, you can select any of the other formats (such as Number, Currency, Accounting) which will also lead to the value in cells being considered as numbers.
Also read: How to Convert Serial Numbers to Dates in Excel
Convert Text to Numbers Using Paste Special Option
To convert text to numbers using Paste Special option:
- Enter 1 in any empty cell in the worksheet. Make sure it is formatted as a number (i.e., aligned to the right of the cell).
- Copy the cell that contains 1.
- Select the cells that you want to convert from text to numbers.
- Right-click and select Paste Special.
- In the Paste Special dialog box, select Multiply within the Operation category.
- Click OK.
Convert Text to Numbers Using Text to Column
This method is suitable in cases where you have the data in a single column.
Here are the steps:
- Select all the cells that you want to convert from text to numbers.
- Go to Data –> Data Tools –> Text to Columns.
- In the Text to Column Wizard:
- In Step 1: Select Delimited and click on Next.
- In Step 2: Select Tab as the delimiter and click on Next.
- In Step 3: In Column data format, make sure General is selected. You can also specify the destination where you want the result. If you don’t specify anything, it will replace the original data set.
- In Step 1: Select Delimited and click on Next.
While you may still find the resulting cells to be in the text format, and the numbers still aligned to the left, now it would work in functions such as SUM and AVERAGE.
Also read: How to Write Scientific Notation in Excel?
Convert Text to Numbers Using the VALUE Function
You can use a combination of VALUE, TRIM and CLEAN function to convert text to numbers.
- VALUE function converts any text that represents a number back to a number.
- TRIM function removes any leading or trailing spaces.
- CLEAN function removes extra spaces and non-printing characters that might sneak in if you import the data or download from a database.
Suppose you want convert cell A1 from text to numbers, here is the formula:
=VALUE(TRIM(CLEAN(A1)))
If you want to apply this to other cells as well, you can copy and use the formula.
Finally, you can convert the formula to value using paste special.
You May Also Like the Following Excel Tutorials:
- Multiply in Excel Using Paste Special.
- How to Convert Text to Date in Excel (8 Easy Ways)
- How to Convert Numbers to Text in Excel
- Convert Formula to Values Using Paste Special.
- Excel Custom Number Formatting.
- Convert Time to Decimal Number in Excel
- Change Negative Number to Positive in Excel
- How to Capitalize First Letter of a Text String in Excel
- Convert Scientific Notation to Number or Text in Excel
- How To Convert Date To Serial Number In Excel?
7 thoughts on “Convert Text to Numbers in Excel”
I have see that most of tips are very helpful
Thanks for this. Didn’t actually work but gave me the idea to multiply the cells by 1, drag and drop and use those instead, which worked
I have numbers from a financial database that are formatted with the suffix for basis points — e.g., 14bp . By default, they are read into Excel as text. I have not found a way to convert these to numbers except for very time-consuming methods: use text to columns to get rid of the bp (which I can do on the initial parse when I still have spaces so that I can use Fixed Width parsing, but if I try to do it after the first parsing, then Excel has stripped the leading spaces and I have to use Delimited parsing and tell it to treat “b” as a separator.) Otherwise, I’m down to using Left(thenumber, len(thenumber)-2), which then may need to be converted into text again.
Do you have a faster way to do this? I thought, perhaps, if I made bp a custom number format Excel would then recognize 14bp as a number when it reads it, but that doesn’t work.
thank you . now i can sum numbers with changing their formats from text to numbers
none of these worked
very helpful thanks allot
Clear precious information