We often see numbers stored in Excel as text which leads to wrong calculations, especially while using the cells in the functions i.e. Average and SUM (since functions like these do not count cells having text values in them). To avoid such circumstances you got to convert the cells containing numbers in the form of text into numbers.

Before proceeding, let’s first find out the reasons why we may have workbooks containing numbers stored in the text format.

1. Using ' (apostrophe) in front of a number

 

• Many of us use apostrophe in front of a number in order to turn it as text. You may have done it during copying data from any database. It may appear that the apostrophe might not show up along with the number, however it will have an impact on the cells since it will force the software to treat them as text.

 

2. The interference of formula in getting numbers (for example- RIGHT, LEFT or MID)

 

• When you tend to extract numerical section of a string of text (sometimes a number’s part) using the functions of TEXT, it results in text format.

 

Let’s find out how to solve such problems

Convert Text to Numbers in Excel

Below is the tutorial showing you the conversion process.

The conversion process of the number actually influences the method that you will use. This tutorial will cover the following.

 

• The use of the option called ‘Convert to Number’

• Text format change into Number/General

• The use of Paste Special

• The use of Text to Column

• The use of TRIM, VALUE and CLEAN combination function

 

Application of Convert to Number option in changing texts to numbers

The number in a cell can turn into a text if apostrophe is used. The easy way to detect this to notice the green triangle showed at top of the cell in the left side.

 

 

For this particular case, follow the process mentioned below to turn the text into numbers:

• Select all the expected text containing cells that you want to convert to numbers

 

 

• The yellow icon with diamond shape should be clicked. It appears on the right side of the top. Now you need to select the option that says ‘Convert to number’.

This process instantly converts all of the texts numbers into numbers. You can be certain of this after viewing that the converted numbers align themselves in the right side of the cells. Earlier these numbers appeared on the left side of the cells.

The change of cell format as a process to convert texts into numbers

If the numbers are changed into text, you can simply change it back into numbers using the format of the cells.

Let’s find out the steps:

• You need to select those cells which you want to convert into numbers.

• Click Home – Number. From the drop-down of Number Format, select General.

 

 

This changes the cell format that you have selected to General. You will see the numbers getting aligned at the right side of the cells. There are options for Number, Accounting and Currency formats there.

Convert Text to Numbers by applying Paste Special Option

In order to apply Paste Special option to convert:

 

• Put 1 in an empty cell of worksheet. You have to make sure that it has been kept in number format (for example it should show at the right sides of the cells)

• The cell containing 1 should be copied

• Now you need to select those cells where you wish to change the form of the texts into numbers

• Right-Click then select the option Paste Special

 

 

• You got to select Multiply that is under Operation category.

 

 

• Now click OK

 

Convert Text into numbers with the help of Text to column

When you have data stored in one single column this method can be suitable.

Let’s discuss the steps:

• Select the cells you are willing to convert to numbers

• Click Data- Data Tools – Text to Columns

 

 

• Now in the wizard of Text to Column:

• Step 1: You need to select the option Delimited and press on Next

 

 

• Step 2: You need to select the Tab option as delimiter. Now press Next.

 

 

• Step 3: In the section of Column data format, select General. There are options for you to specify where you want your result and set that destination. In case you do not specify anything, the system would replace the set with original data.

 

 

Here you can find those resulting cells in text format still, and to the left the numbers are aligned. Functions like Average and Sum should work now.

Using VALUE Function to convert text formats to numbers

Use the VALUE, CLEAN and TRIM combination in order to convert the text formats into numbers.

 

• VALUE is the function which converts all text representing a number to a specific number

• TRIM works for removing trailing or leading spaces

• The CLEAN, on the other hand, cut excess spaces and characters that are non-printing. Usually these numbers sneak in when you import them from the data. It can also happen if you download the data from a certain database.

 

For example, if you are willing to change cell A1 from a text format to a number format, the formula given below should be followed:

= VALUE (TRIM(CLEAN(A1)))

In case you are willing to apply this formula to other specified cells you like, you may copy the formula and use it.

Finally, you need to use Paste Special in order to convert this formula.