How to Convert Text to Numbers in Excel (8 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes the numbers on your worksheet behave strangely; they don’t carry out arithmetic operations as they ought to. This is because, despite their appearance as numbers, they are formatted as text. So, in this article, we will show how to convert text to numbers in Excel in 8 different methods.


Download Practice Workbook

You can download the practice workbook from here.


8 Easy Ways to Convert Text to Numbers in Excel

In this article, we will discuss how to convert text to numbers in Excel. Firstly, we will apply cell formatting. Secondly, we will use the error-checking option. Thirdly, we will use the Text to Columns option. Then, we will opt for the Paste Special option. After that, we will use the VALUE function. Then, we will simply multiply the data by 1 to turn them into numbers. Next, we will go for a VBA code. Finally, we will use the Power Query to convert text to numbers in Excel.


1. Applying Cell Formatting

In this method, we will format the cells to hold numbers to convert the data to numbers from text.

Steps:

  • Firstly, select the cell range containing the text data.
  • In this case, it will be C5:C10.
  • Then, go to the Home tab.
  • From the Home tab, select Numbers.
  • Finally, from the drop-down choose Number.

cell formatting to convert text to numbers in excel

  • As a result, all the text data will be converted to numbers.

Read More: How to Convert Number to Text in Excel (4 Ways)


2. Using Error Checking

Error checking dialogue box displays any error present in the worksheet. In this instance, we will use that to convert text to numbers.

Steps:

  • Firstly, select the C5 cell and you will see the error checking sign beside it.

using error checking to convert text to numbers in excel

  • Then, click on the error checking sign, and form the drop-down select Convert to Number option.
  • As a result, Excel will turn the text to a number.

selecting from error checking to convert text to numbers in excel

  • Repeat the process for the rest of the data cells.

  • Consequently, all the cells will convert to numbers.

Read More: How to Convert Number to Text with Green Triangle in Excel


3. Using Text to Columns Option

In this example, we will use the Text to Columns option to convert the text to numbers in excel.

Steps:

  • To begin with, choose the C5 cell.
  • Go to the Data tab.
  • Then, select Data Tools.
  • Finally, from the drop-down, choose Text to Columns.
  • Consequently, a prompt will appear.

applying text to columns option to convert text to numbers in excel

  • From the prompt, first, select the Delimited oval.
  • Then, select Next.

selecting delimited oval to convert text to numbers in excel

  • In the next prompt, check the Tab box.
  • Again press Next.

  • In the final prompt, mark the General box.
  • Finally, click Finish.

  • As a result, the text will be converted to a number.

  • Do this for the rest of the data cells.

Read More: How to Convert Number to Text with Commas in Excel (3 Easy Methods)


4. Utilizing Paste Special Option

In this case, we will copy a blank cell and use the Paste Special option to paste it into our desired cell to convert the text it contains to a number.

Steps:

  • Firstly, select the D5 cell and copy it using the Ctrl + C shortcut.

copying empty cell to convert text to numbers in excel

  • Secondly, select the C5 cell.
  • Then, go to the Home tab.
  • From the Home tab, select the Paste option.
  • Finally, from the drop-down choose the Paste Special command.

selecting special paste command to convert text to numbers in excel

  • Then, from the Paste Special dialogue box, select the oval beside the Add option.
  • Finally, click OK.

selecting add oval to convert text to numbers in excel

  • As a result, the text in the C5 cell will turn into a number.

  • Repeat the same steps for the rest of the data cells.

utilizing paste special option to convert text to numbers in excel

Read More: How to Convert Number to Text without Scientific Notation in Excel


Similar Readings


5. Applying VALUE Function

A text string that represents a number is changed into a number using the VALUE function. In this instance, we will use the function to do it.

Steps:

  • To begin with, select the D5 cell and write the following formula in the cell,
=VALUE(C5)
  • Then, hit Enter.

  • As a result, the function will turn the text into a number.
  • Finally, lower the cursor down to the last data cell to auto-fill the cells.

applying value function to convert text to numbers in excel

Read More: How to Convert Number to Text in Excel with Apostrophe


6. Multiplying by One

In this case, we will apply a neat mathematical technique to convert texts to numbers.

Steps:

  • To start with, select the D5 cell and type the following formula in the cell,
=C5*1
  • Then, hit the Enter button.

  • Consequently, the formula will turn the text into a number.
  • Finally, move the cursor down to the last data cell to auto-fill the cells.

multiplying by one to convert text to numbers in excel

Read More: How to Convert Number to Text with 2 Decimal Places in Excel (5 Ways)


7. Applying VBA

In this method, we will resort to a simple VBA code to do the task.

Steps:

  • Firstly, go to the Developer tab in the ribbon.
  • From there, select the Visual Basic option.
  • Consequently, the Visual Basic window will be opened.

  • After that, in the Visual Basic tab, click on Insert.
  • Then, select the Module option.
  • Consequently, a coding module will appear.

  • After that, write down the following code in the module and save it.

Sub convert_text_to_numbers()
With Range("C5:C10")
.NumberFormat = "General"
.Value = .Value
End With
End Sub 
  • Finally, click on the Run tab.
  • To run the code, choose the Run command from the drop-down menu.

  • Consequently, we will see that Excel has executed the code.
  • All the texts in the C5:C10 range will be converted to numbers.

Read More: Excel VBA: Convert Number to Text with Format (A Total Guide)


8. Applying Power Query

Power Query is a fantastic tool for performing any type of necessary data modification. It can also be used to convert text to numbers, without a doubt.

Steps:

  • Firstly, select the Data tab from the ribbon.
  • Then, select the From Table/Range command.

  • Select the dataset as your table range.
  • Finally, click OK.
  • As a result, the Power Query window will be opened.

  • In the Power Query window, select the numerical sign beside the “Salary”  column.
  • From the drop-down option, select Whole Number.

  • Then, go to the Home tab.
  • Select the Close & Load option.
  • Finally, from the drop-down select the Close & Load command.
  • As a result, a new window will be opened.

  • We will see that all the text values are converted to numbers.

Read More: Excel VBA to Convert Number to Text (4 Examples)


How to Bulk Convert Text to Number in Excel

In this method, we will bulk convert texts to numbers. We will make use of the Paste Special command to do so.

Steps:

  • Firstly, select the D5 empty cell and press Ctrl+C to copy it.

  • Then, select the cells in the range C5:C10 and right-click.
  • From the available options, select the Paste Special command.
  • Consequently, the Paste Special dialogue box will appear on the screen.

  • From the dialogue box, first, mark the Add oval.
  • Then, select OK.

  • Consequently, all the texts will be turned into numbers in bulk.


Conclusion

As we can see, there are numerous ways to convert text into numbers in excel. It is best practice to transform text formatted numbers into actual numbers when needed in a mathematical calculation in order to conduct an advanced arithmetic operation.


Related Articles

Siam Hasan Khan
Siam Hasan Khan

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 an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

6 Comments
  1. 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. Thanks for sharing this article. Was very easy to follow. Waiting for more

  3. Using numbervalue function

  4. Keep it up. Your persistence and hard work is catching my attention. I. will like you to do something on macro. Your step by step method is easy to follow. I have read a lot of materials on macro and understood it but could not get any of my macros to do the task, meaning something is missing. Can you use your detail, honest, pain staking approach to brake
    this impasse. I term your style of teaching unique and it will get you to the top.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo