Convert Numbers Stored as Text to Numbers in Excel

Add to Favorites

I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situation often happens when you export data from another program to Excel and it can wreak havoc in your spreadsheets.

Here are three simple ways to converts numbers to their proper number format:

(Don't forget to download the accompanying workbook so you can follow along with these examples.)

Method 1 - Use Excel's Built-in Help

(if you have thousands and thousands of rows, this method will take too long and you should use another method listed below)

This method will not always work, but when it does, it's by far the easiest.

When you have a set of numbers stored as text that Excel thinks should maybe be stored as numbers, it will warn you.  You will see a little green triangle in the upper-left corner of a cell:

When you see this, you can quickly convert all of those numbers to the correct format.

Let's start with the first column of numbers (Column A).

Note: This will only work on cells that are right next to each other.

  1. Select all of the cells that have the green triangle:
  2. You will notice a small box with an exclamation point appear, in this case over cell B2.  Click that and then click Convert to Number:
  3. That's it, now all of these numbers are stored as numbers:
    You can tell that these are now stored as numbers because the numbers are now on the right-side of the cells instead of the left-side.

Method 2 - Paste Special

Another way to convert the numbers to the number format is to multiply the list by a number, use 1 so that it doesn't change any of the numbers.

We will work on column C this time.

  1. Add 1 to an empty cell on the worksheet:
  2. Copy the cell (Ctrl + C) where you just input 1:
  3. Select the range of numbers to convert:
  4. Right-click over the list of numbers and select Paste Special:
  5. Select Multiply and hit OK:
  6. Now, the entire column is formatted as a number.  Delete the 1 in cell D1 and that's it.:

Method 3 - Change the format of the Column

This is the simplest method but I put it at number 3 because it only works on the most basic numbers formatted as text.  Let's look to column E now.  Every number is formatted as text, but changing the format will only work on some cells.

  1. Select the cells you want to change:
  2. Go to the Home tab and in the Number box change where it says "Text" to "Number":
  3. Look at the numbers now, this method only worked on the numbers from E7 to E11:

For those numbers you are done.  But this method did not work on the numbers from E1 to E6.  This is because those numbers have either a space or an apostrophe in front of them and simply changing the format will not fix that issue.  This is why method 1 and 2, though they require a little more work, will get you more consistently accurate results.

Method 4 - Text to Columns and Formatting

This method involves two big steps but it is fairly easy to use.  The problem with it, why I mention it last, is because it is easy for forget to perform the last step and because you leave some of the guess-work up to Excel, which I never like to do.

Let's go back to column E.

  1. Select the column of numbers:
  2. Go to the Data tab and click Text to Columns:
  3. In the window that appears, don't touch anything, just click the Finish button:
  4. Now, select the column of numbers again (we are almost done):
  5. Go to the Home tab and, in the Number box, choose Number:
  6. That's it!

You can remove the decimal places if you want by clicking the Decrease Decimal button, which is located inside the Number box on the Home tab.

Notes

I personally prefer Method 2 when I have to do this on a large list because it seems to work in the most consistent manner across all of my files.

If you need to perform this conversion on numbers that are not in a list or right next to each other, Method 2 is also probably the best option for doing that.

Remember that sometimes Excel won't tell you when a number is not formatted as a number; you need to be on the lookout for hidden apostrophes, leading and trailing spaces, and, of course, different formats for the cells.


Downloadable Files: Excel File

Similar Content on TeachExcel
Convert Scientific Notation to Numbers in Excel
Tutorial: How to convert scientific numbers to show their full amount; this method also allows you t...
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...
Convert Numbers into True and False in Excel
Tutorial: How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very impor...
Get Text from Comments in Excel Including the Author of the Comment - UDF
Macro: Output all text from a cell comment, including comment author, with this UDF in Excel. Thi...
Filter Data in Excel - AutoFilter
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...