How to convert text to number in Excel?

When the numeric values are stored as text, the user cannot perform numeric operations on it. Number stored as text can generate unexpected errors on performing operations on them. The Excel users have to convert the text to number to perform the arithmetic operation on values that stored as a text string.

Excel allows its users to convert the number to text as well as text to number. In this chapter, we will show you several ways to convert the text data (number stored as text) to number.

How to find numbers stored as text?

Numbers store as text can mislead the result and lead to incorrect calculations when performing operations like SUM or AVERAGE. It is easy to determine when a number is stored as a text.

With the help of given points, you can determine the number stored as text -

  1. Sometimes, you see a green color small triangle at the left corner of the cell. This is because of when the number is converted to the text format by placing an apostrophe before the number.
    How to convert text to number in Excel?
  2. Sometimes, you don't see a green color triangle at the left corner of the cell and still numbers are in text format. You will notice that if the number is in text format, they are aligned on the right side of the cell.
    How to convert text to number in Excel?
  3. When the number is stored as text, they are default aligned to the right in the cell. Whereas, generally the number is aligned to the left in the cell.

Difference between number and text format

These given differences will help you to understand the numeric value as number format and in text format.

Number as Number formatNumber as text format
Number is aligned to the right in the Excel cells.Number stored as a text is aligned to the left in the Excel cells.
On selecting the numeric values, Excel shows numeric functions like Average, Sum, Count in the status bar.On selecting the numbers stored as text, Excel displays only COUNT method in the status bar.
You will see a normally stored value as numbers.You may see a leading apostrophe sign in the formula bar corresponding to the value.
You can perform numeric operations on numbers without any error.Any numeric calculation will lead to generating the error.

Methods to convert text to number

Excel enables the various ways to convert the text (number as text) to number. These are the different methods that we will discuss in this chapter -

  1. Using Convert to Number method
  2. Using Paste Special
  3. Using Text to Column
  4. Change the format to text to General/Number

Convert text to number using Convert to Number method

When the apostrophe is added before a number, it changes from number format to text format. In that case of converted number to text format, a green color small triangle is added to the top of left corner of the cell.

Use the following steps to convert the text to number -

Step 1: Select all the targeted cells you want to convert from text to number.

How to convert text to number in Excel?

Step 2: Click the yellow diamond icon that appears near the selected cells (at the top right).

How to convert text to number in Excel?

Step 3: Now, select and click the Convert to Number option.

How to convert text to number in Excel?

Step 4: It will immediately convert the number stored in text format, to the number format.

How to convert text to number in Excel?

Convert text to number using Paste Special

Paste Special is another way to convert the text to number. Using this, you can convert the number stored in text format back to number format. Except the above methods, this method follows a long process and takes more steps.

It is a perfect method for converting the text to number. Follow the steps carefully -

Step 1: Select all the cells you want to convert from text format to number format.

How to convert text to number in Excel?

Step 2: Set the format to General inside number group.

How to convert text to number in Excel?

Step 3: Now, copy a blank cell using the Ctrl+C shortcut copy key.

How to convert text to number in Excel?

Step 4: One more time, select the text formatted cells for conversion and right-click on the selected cell then choose the Paste Special.

How to convert text to number in Excel?

Alternatively, you can also press the Ctrl+Alt+V shortcut key to open the Paste Special dialogue box directly.

Step 5: A dialogue box panel will open where choose Values in Paste section and Add in Operation section then click OK.

How to convert text to number in Excel?

Step 6: If you have performed all steps correctly, your data is converted from text to number.

How to convert text to number in Excel?

You will note that after converting the text to number, number is aligned to the right in the cell as the below Wireless and Speaker price.

Convert text to number using text to column

As we told you that several methods are available in Excel to convert the number stored as text to the number. You can use the text to column option is one of the conversion methods in Excel. It is a formula-free way to convert the text to number.

Following are the simple steps for text to number conversion -

Step 1: Select the targeted cells for the text to number conversion.

How to convert text to number in Excel?

Step 2: Go to the Data tab in the Excel ribbon and click Text to Column button inside the Data Tools group.

How to convert text to number in Excel?

Step 3: A wizard will open where select Delimited radio button and click Next.

How to convert text to number in Excel?

Step 4: On the next wizard, keep the options default and click Next.

How to convert text to number in Excel?

Step 5: Select the column data format to General and click the Finish button to end the process.

How to convert text to number in Excel?

Step 6: Selected number stored as text is now converted to number format.

How to convert text to number in Excel?

Convert text to number using by changing cell format

Sometimes, you don't see a green color triangle at the left corner of the cell, as you see in all the above examples. But still, the numbers are in text format. You can find that the numbers are aligned on the right side of the cell. It means the numbers are in text format.

How to convert text to number in Excel?

When a number is stored as text, you need to convert it back to as number to perform any numerical operations on it. For this type of number stored as text, you have to convert it back to number format. You can easily convert it by changing the cell format from text to number.

Following are the simple steps for it -

Step 1: Select all the cells (text formatted cells) for text to number conversion.

How to convert text to number in Excel?

Note: Do not select column header.

Step 2: Inside the Home tab, you will see that the selected data is stored as text in the Number section.

How to convert text to number in Excel?

Step 3: Here, change the format from Text to General or Number.

How to convert text to number in Excel?

Step 4: Text alignment will automatically change from right to left when the number is changed to the general or number format.

How to convert text to number in Excel?

You can now perform any arithmetic or numeric operation on it.






Latest Courses