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

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.

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

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

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

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.

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

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

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

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.

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

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.

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

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

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

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

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

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.

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.

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.

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

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

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