Convert Text to Number in Excel

This tutorial shows how to convert text to numbers in Excel using text to column, Formulas, Paste Special, error checking, or VBA.

What if you want to work with numbers, but the formula returns errors? First, you should check the given cell: is the value in a number format? Or is it a text? The problem: in some cases, numbers are formatted as text. We will show you how to avoid issues!

How to check numbers formatted as text in Excel

Excel has a built-in error checking feature. If you see a green marker (small triangle) in the top left corner of a cell, it is a number that is stored as text. Okay, sounds good, but we do not have any visual alerts in some cases.

Let us see how to identify numbers formatted as text in Excel:

Numbers

  • The numbers are aligned in the cell right by default.
  • Select multiple cells and check the status bar on the bottom-right section. You will see the average, count, and sum values if you have numbers.
The numbers are aligned in the cell right by default.

Text

  • If the cell contains the text, you will see a right-aligned value in the cell.
  • If you select a range that contains text, the status bar shows only the count value.
  • Click on the formula bar: it is a text value if the first character is an apostrophe.
How to identify numbers formatted as text in Excel

How to convert text to numbers in Excel

In Excel, we have many options to convert text to numbers. This guide will cover easy and fast methods.

Convert text value to number in Excel with error checking

Let’s talk about the small green triangle at the top-left corner of cells.

Click inside the cell! A small floating window will appear, and the description will indicate the reason for the error and gives solutions. The one-step conversion is a user-friendly feature!

Use the drop-down menu and select the “Convert to Number” command.

Convert text value to number in Excel with error checking

After clicking it, Excel will convert the text to a number, like in the result below:

after the conversion we have numbers

Everything looks fine; you see the status bar’s average, sum, and count results.

How to convert text to numbers using the “Paste Special” command

Here are five simple steps to convert text to number:

  1. Select a cell and type 1 into it, then press Enter.
  2. Use the CTRL + C shortcut to copy the cell content into the Clipboard.
  3. Select the cells or range of cells containing numbers but stored as text.
  4. Right-click, then choose Paste special.
  5. Under the Operation Group, click multiply, and then click OK.

In the picture below, you can follow the steps:

How to convert text to numbers using the “Paste Special” command

Convert Text to Numbers using multiply by 1

If you don’t use the ‘Paste Special” method yet, there is a workaround with the text to number conversion method:

  1. Insert a helper column
  2. Type =1 into all necessary cells
  3. Enter a simply formula =(Text value) * 1

Here is an example:

The result is the same, and now we have numbers, not numeric characters, stored as text.

Use a function to convert text to number

We love built-in Excel functions. For example, the VALUE function converts a text string representing a number to a number.

We generated text values in the “Sales” column; some string contains extra spaces and apostrophes. First, in column E, use the LEN function to get the length of the text.

After that, use the VALUE function:

=VALUE(D3)

Use a function to convert text to number

The result looks great! Without using CLEAN, TRIM, or other data cleaning functions, the VALUE function converts text to numbers quickly.

(*In the example, we use Microsoft365, the latest Excel version for subscribers.)

Convert string to number with Text to Columns

Last but not least we have to talk about a well-known solution. Excel’s Text-to-column function uses a wizard that is useful for beginners. Good to know that the function works with a single column only.

Let’s see the steps:

First, select the range of cells containing the text you want to convert to numbers. After that, select the Data Tab on the ribbon. Finally, choose the Text To Columns command.

select all the cells that you want to convert from text to numbers

A new window will appear; select “Delimited” then click Next.

convert text to columns wizard

Under the “Delimiters” group, select “Tab“, then click Next.

delimiter window

Select “General“. The “General” option converts numeric values to numbers, date values to dates, and all remaining values to text. In the example, we want to replace the column. If you need to move the converted cells to another Worksheet or range, change the destination using the “Destination” option. Finally, click “Finish“.

step3

Excel converted text to numbers!

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.