Convert Text to Number in Excel

This tutorial shows how to convert text to numbers in Excel using text to columns, 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 stored as text. Okay, that sounds good, but we sometimes do not have visual alerts.

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 at the bottom right. 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 give 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; 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:

Convert-Text-to-Numbers-using-multiply-by-1

The result is the same; 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 strings contain 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! The VALUE function converts text to numbers quickly without using CLEAN, TRIM, or other data-cleaning functions.

(*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 discuss a well-known solution. Excel’s Text-to-column function uses a wizard that is useful for beginners. It is 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.

text-to-columns-step1

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

convert-text-to-columns-wizard

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

delimiters

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!

If you want to learn all about Excel Formulas, visit our definitive guide.

Additional resources: