How to Convert Text to Numbers in Excel

Last updated on July 30th, 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.

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

how to convert text to numbers in excel

Applying number format instead of Text

  • 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

Number Formatting

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.

Error Check to Convert Text-formatted Numbers into Actual Numbers

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

Paste special function to convert text to number

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.

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

Convert Month name to Month Number in Excel

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.

TIMEVALUE Function to Convert Text to Time in Excel

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.

Excel VBA to Convert Text to Number

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

Conclusion

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.

Read More…


Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
Hello!

Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

2 Comments
  1. Reply ahmadenns@gmail.com'
    Ahmad April 5, 2018 at 6:47 AM

    I have prepare a template in Excel .I want macro instead of goal seek.i want the value in column “”B”” should be such that the computed value of discharge match with the design discharge.i am forwarding the sheet.plz give me help

  2. Reply almahmudsaif@gmail.com'
    Saif Prottoy April 5, 2018 at 10:18 AM

    Thanks for sharing this article. Was very easy to follow. Waiting for more

    Leave a reply