Convert text to number Excel entire column

A common frustration in Excel is when numbers are stored as text.

You will not be able to perform Excel tasks such as mathematical calculations, create charts from the values, or group them into ranges while the numbers are stored as text.

In this tutorial, you will learn how to recognize numbers stored as text and multiple ways to convert text to numbers.

Here’s what we’ll cover:

You can follow along with this workbook.

1. How to check if a value is numeric or text

It is not always clear that a number is stored incorrectly. It can appear as a number, yet be stored as text. This causes much confusion for Excel users.

However, there are some things to look out for, and ways to accurately check it.

Firstly, Excel will always display text to the left of a cell and numeric values to the right of a cell. So, if a value is left-aligned, it is probably text.

This is just a clue and not guaranteed, because users can change the alignment of cell values. However, it is a great place to start and a good clue to look out for.

Another way of checking is to select the range of values and look at the calculations down on the Status Bar.

If the values are all numeric, you will probably see average, count and sum calculations. And if any are text, only the count is performed.

Once again, these calculations can be changed by users so it’s not a perfect check. However, it will work 95% of the time and is a fast and easy way of checking.

To be completely sure, we can use an Excel function named ISNUMBER. This function will return TRUE if the value is numeric and FALSE if it is not.

You only need to provide the ISNUMBER function with the value to check. In this example, the following formula was entered into cell C2.

=ISNUMBER(B2)

I have converted some of the values to numbers. So, even though they all look like numbers, the ISNUMBER formula returns FALSE, making it easy to identify the problem values.

2. The ‘Convert to Number’ Excel option

The first technique we will look at to convert text to numbers is the ‘Convert to Number’ option provided in Excel.

It is quick and simple, but it is unfortunately not always available to you.

This option is available when numbers are stored as text as a result of downloading the data from a website or some database, or maybe copied and pasted between programs.

Also, it may be that someone accidentally typed an apostrophe before the number. This tells Excel to store the number as text, and is very useful for entering ‘fake’ numbers such as phone numbers and ID numbers.

A green triangle is shown in the top left corner of the cell if this option is available to you.

This triangle means that the value has failed one of Excel’s error check rules, and is used to identify other errors outside of just recognizing numbers stored as text.

To convert to numbers;

  1. Select the range of cells to change.
  2. Click the exclamation mark icon and click Convert to Number.

The values are converted. The green triangle has disappeared and the values are now right aligned. This is how we expect to see numbers.

3. Change text to numbers with Paste Special

Paste Special in Excel is a fabulous tool. It is no surprise that it can help us convert text to numbers too.

To do so, we need to use Paste Special to perform a simple calculation. When a text value is used in a calculation, the result is numeric.

  1. Type the number 1 in a cell, any cell.
  2. Copy that cell value.
  3. Select the range of values you need to convert to numbers.
  4. Click Home > Paste list arrow and Paste Special.

5. Select Values and the Multiply operation. Click Ok.

4. Using Text to Columns to convert text

Text to columns is another great way to quickly convert text to numbers.

  1. Select the range of values.
  2. Click Data > Text to Columns.
  3. Step 1 of the Text to Columns wizard regards splitting text between columns. We do not need this, so click Next.

4. In step 2 remove all delimiter checkboxes.

5. Step 3 asks about the value formatting. Leave it as General (the date option is great when working with date values). Click Finish.

You probably didn’t change an option during the Text to Columns process, and we could have clicked Finish before step 3.

However, you may have to consider if your value contains delimiters such as spaces or commas.

I like this method of converting text to numbers. It is quick and simple.

5. Convert text to numbers using formulas

The previous methods are great, but they require manual intervention. A user will need to perform the steps to convert the text to numbers.

With a formula, we can automate the solution. It will not change the value in the cell containing text, but rather, create a clean version in another cell.

If the data is regularly changed, a formula will ensure that the values fed to a PivotTable, chart or other formulas continue to be numeric. And no manual intervention is required.

Using a formula is also great for combining with other functions. For example, converting results from text functions into numbers.

That is the example we will look at here.

In this example, we have data downloaded from a source and the values in column B are stored as text due to the dollar sign.

Using a formula, the number has been extracted successfully. However, the RIGHT and LEN functions used in the formula are text functions. So, the result is returned as text.

The VALUE function of Excel converts text that represents a number to a number.

Using this function, the result of the RIGHT function is converted from text to number.

The values can then be formatted as required. For example, in the accounting format with the dollar sign.

Final thoughts and further reading

So there you have it: some simple methods for converting text to numbers. If you’re getting to grips with Excel in your quest to become a data analyst, you can try a free data analytics short course here. Keen to learn more Excel functions? Check out the following:

Convert text to number Excel entire column

When you enter numeric data into your spreadsheet, do you pay attention to how it’s formatted? Many times, numbers are formatted as text which can wreak havoc with formulas. Here are various ways to convert text to numbers in Excel.

Depending on how your number is entered as text, one method below may work better for you. For instance, you might have many nonadjacent cells where converting the cell format would be tedious. Or you might not see the warning symbol in order to use that method.

Change the Cell Format

A quick and easy way to convert text to a number is by changing the cell format on the Home tab. Use the drop-down box at the top of the Number section. Choose “Number” from the list.

Convert text to number Excel entire column

Alternatively, right-click the cell(s) and pick “Format Cells.” Select the Number tab and pick “Number” on the left. Optionally adjust the Decimal Places and click “OK.”

Convert text to number Excel entire column

You can also use the General format instead of Number if you like. General is Excel’s default number format. The difference with General compared to other number formats is that it uses scientific notation for numbers over 12 digits and rounds decimal numbers if the cell isn’t wide enough to accommodate the entire string.

RELATED: How to Convert Text to Date Values in Microsoft Excel

Use the Warning Symbol

If you enter your data and see a warning symbol on the bottom left corner, this is a fast way to convert the text. When you hover your cursor over the warning, you’ll see an explanation as in the screenshot below.

Convert text to number Excel entire column

Click the drop-down arrow next to the warning and choose “Convert to Number.”

Convert text to number Excel entire column

This removes the warning and formats the text as a number.

Select Text to Columns

Maybe you have an entire column of numbers formatted as text. Using the Text to Columns feature is the way to go. Note, however, that you can also use this option for a cell range.

RELATED: How to Use Text to Columns Like an Excel Pro

Select the column, go to the Data tab, and click “Text to Columns” in the Data Tools section of the ribbon.

Convert text to number Excel entire column

When the wizard displays, simply click “Finish.” Alternatively, you can click “Next” through each step without making any changes and hit “Finish” at the end.

Convert text to number Excel entire column

This changes the format to Excel’s default General format for numbers. You can leave the format as-is or select the cells and choose “Number” in the Number section on the Home tab as described earlier.

Use Paste Special

You might have many cells or multiple columns that you need to convert to numbers from text. In this case, you can use the Paste Special with the Multiply operator.

RELATED: How to Add or Multiply Values with Paste Special in Microsoft Excel

Go to a blank cell that’s not formatted as text but as Number or General instead. Type the number 1 and press Enter or Return.

Convert text to number Excel entire column

Copy the cell by right-clicking and selecting “Copy” or clicking the Copy button in the Clipboard section of the Home tab.

Convert text to number Excel entire column

Select all of the cells that you need to convert to numbers from text.

Either right-click and choose Paste Special > Paste Special or go to the Home tab and choose “Paste Special” in the Paste drop-down box.

Convert text to number Excel entire column

Leave Paste set to “All” at the top. Under Operation at the bottom pick “Multiply”. Click “OK.”

Convert text to number Excel entire column

This multiplies all values by the number 1, giving you the same numbers as results, and changes the format to that of the copied cell, either General or Number. You can then remove the number 1 you used.

Convert text to number Excel entire column

Insert the VALUE Function

The VALUE function gives you one more way to convert text to numbers in Excel. With this option, you’ll use a column to the right to convert the text.

RELATED: How to Add and Remove Columns and Rows in Microsoft Excel

If you need to insert a new column, right-click the column to the right of your text formatted column and pick “Insert.”

In the top cell (or second cell if you have a column header) enter the following replacing the cell references with your own.

=VALUE(A2:A6)

Convert text to number Excel entire column

Alternatively, you can enter a single cell reference in the parentheses to convert one cell. Then, copy the formula down to the remaining cells using the fill handle.

=VALUE(A2)

Convert text to number Excel entire column

You can then use the numbers in the new column as needed and do what you please with the original column.

Once you convert your text to numbers in Excel, your formulas are ready for their calculations with correctly formatted numbers!