How To Convert Text To Number In Excel

Wondering how to convert text to number in Excel? Read the article to know about it.

It’s common to find numbers stored as text in Microsoft Excel which can lead to incorrect calculations when you use these cells in Excel functions such as SUM calculation and AVERAGE determination. In such cases, you need to convert cells that contain numbers as text back to numbers.

Here are a few reasons why you may have a workbook that has numbers stored as text.

Using ‘ (apostrophe) before a number

A lot of people enter apostrophes 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.

Convert Text to Numbers in Excel

Let us now learn how to convert text to numbers in Microsoft 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 article.

• 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 functions.

Convert Text to Numbers Using the ‘Convert to Number’ Option

When an apostrophe is added to a number, it changes the number format to text format. In this case, you’ll notice that there is a green triangle at the top left part of the cell.

You can easily convert numbers to text by following these steps:

1. Select all the cells that you want to convert from text to numbers

2. Click on the yellow diamond shape icon that appears at the top right. From the menu that appears, select the ‘Convert to Number’ option.

This would instantly convert all the numbers stored as text back to numbers. 

Also Read
How to wrap text in Excel
How to split cells in Excel

Convert Text to Numbers by Changing Cell Format

When the numbers are formatted as text, you can convert them 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. 

Convert Text to Numbers Using Paste Special Option

To convert text to numbers using Paste Special option:

1. 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).

2. Copy the cell that contains 

3. Select the cells that you want to convert from text to numbers.

4. Right-click and select Paste Special.

5. In the Paste Special dialogue box, select Multiply within the Operation category.

6. Click OK.

Also Read
How to separate text in Excel
How to unhide columns in Excel

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:

1. Select all the cells that you want to convert from text to numbers.

2. Go to Data –> Data Tools –> Text to Columns.

3. 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.

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.

Convert Text to Numbers Using the VALUE Function

You can use a combination of VALUE, TRIM and CLEAN functions to convert text to numbers.

• VALUE function converts any text that represents a number back to a number.
• The TRIM function removes any leading or trailing spaces.
• The CLEAN function removes extra spaces and non-printing characters that might sneak in if you import the data or download it from a database.

Suppose you want to 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.

CONCLUSION

MS Excel is a Microsoft software program that uses spreadsheets to organize numbers and data using formulas and functions. Ordinary people can use Microsoft Excel for simple tasks like data filtering and using pivot tables, as well as more complex ones like concatenation function.

EasySheet is a tech blog where you can discover fantastic Excel hacks, tips, and tricks. Our goal is to make Excel more accessible to beginners and to provide them with the EasySheet as a valuable resource for all of their questions.

If you need assistance with Microsoft Office products like Excel, Word, PowerPoint, Publisher, or anything else, come to us!