How to Convert Text to Numbers in Excel
In this article, I tried to sum up some of the processes on how to convert text to numbers in Excel.
In many cases when you import data from an external source or create a file in a different program, numbers are recognized as text in Excel. The reasons for formatting text into numbers may be for math operation or to use functions like MATCH, LOOKUP, VLOOKUP to work properly.
In excel there is a feature to convert text to numbers. This feature works fine with small table/short range of cells, but when you work with a long range of cells problems may occur as this feature may not work properly.
Applying number format instead of Text
You can use the number format if your spreadsheet deals with numbers only. This can also be used later but the cells with the values that you want to convert should not contain any leading or trailing spaces. Use the TRIM function to neglect spaces. The procedure of applying number format is given below.
- 1st select the cells that contain the text-formatted numbers
- Navigate to the Home tab -> Number group
- From the number format, drop-down list select the necessary number format
- To get the extended set of the number from the formatting options, click on the Dialog Box Launcher next to Number
- A window titled Format Cells will come up and there you can select the necessary category in the list under the Number tab
This is one way to convert text formulated numbers into actual numbers.
Using Error Check to Convert Text-formatted Numbers into Actual Numbers
This is the easiest way to format text formulated numbers into actual numbers. While importing numeric data from other sources, or while entering text formatted numbers you will see some small green triangles in the upper-left corner of cells containing numbers. These green colored triangles are produced because the numbers are saved as text in your spreadsheet.
To convert them into actual numbers 1st you have to select the cells that you want to format. Then you have to click the exclamation sign beside the selected cell/range. Now select the option Convert to Number.
By clicking Convert to Number. You will see all the selected cells are now containing actual numbers instead of text. You can verify that by seeing that the formatting is changed from Text to General/Number.
Use the Paste special function to convert text to number in Excel
The numbers which are formatted in general can be made into actual numbers by using this option. Just like the previous option make sure that your cells don’t contain any spaces. The process is stated below.
- 1st select the cells to convert them into number format
- Copy them and indicate another range where you want to paste the numbers
- Instead of pasting them click on the paste special option
- Click Add in the Operation section of Paste special dialogue box and then press OK
The alignment of cells will change to the right, that means the numerical values become actual numbers.
Note: Don’t perform the paste special option in the same cells from which you copied. In the following picture, you can see every number in each of the cells is doubled because we perform the paste special option in the same cells.
Using Excel’s VALUE Function to Convert Text to Numbers
In Excel, the VALUE function can be used to convert numbers that have been used as text data so that they can be used in calculations. Normally applying some basic formulas like SUM or AVERAGE excel automatically converts these to actual numbers. Problems occur when you are going to perform some advanced calculations. The following picture shows formula and result to convert text to numbers.