Last updated on April 19th, 2018
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.
Table of Contents
- 1 Applying number format instead of Text
- 2 Using Error Check to Convert Text-formatted Numbers into Actual Numbers
- 3 Use the Paste special function to convert text to number in Excel
- 4 Using Excel’s VALUE Function to Convert Text to Numbers
- 5 How to Convert Month name to Month Number in Excel
- 6 Using the TIMEVALUE Function to Convert Text to Time in Excel
- 7 Using Excel VBA to Convert Text to Number
- 8 Conclusion
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 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.
How to Convert Month name to Month Number in Excel
In this example, we will learn how to convert month`s name into its equivalent month number. In our example, Column A contains the 12 months name. We want to convert these months name into its equivalent month number. The process is given below.
- Type the formula =MONTH(DATEVALUE(A2&”1″)) in cell B2
- Drag the formulated cell B2 to B13
It will show the months the number in column B. The following picture shows the result of this example.
Using the TIMEVALUE Function to Convert Text to Time in Excel
If in your spreadsheet you have values that have been inserted as time but are actually text values, then you have to use the TIMEVALUE function to convert these text values as time values. Let’s say in cell A2 we have the text string of time. We want to convert it to a valid time value in another cell. We will use the formula =TIMEVALUE(A2) for this. Using this value, we will find a numeric value which is not an actual time. We will need to format this numeric value into time value to show the exact time value.
The following picture shows how the process is done.
Using Excel VBA to Convert Text to Number
To convert text to numbers in excel 1st you have to open the visual basic editor from the Developer tab. Then in the blank box apply this code and run it. You will see all your text formatted cells will convert to the general format.
There are so many codes available to do this. We used a shortcode to perform this task. The code that I used is given below which I collected from this link https://stackoverflow.com/questions/44826966/vba-how-to-convert-value-of-cell-from-text-to-number
Sub macro() Range("A1:C10").Select 'specify the range which suits your purpose With Selection Selection.NumberFormat = "General" .Value = .Value End With End Sub
As we can see there are so many methods available to convert text into a number. To perform an advanced arithmetic task, it is good a practice to convert the text formatted numbers into actual numbers if they require in any mathematical calculation.
I hope, by reading this blog you will find a good solution for converting the text strings to numbers. If you have any questions please write it in the comment section.