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.
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.
- As a result, all the text data will be converted to numbers.
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.
- 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.
- Repeat the process for the rest of the data cells.
- Consequently, all the cells will convert to numbers.
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.
- From the prompt, first, select the Delimited oval.
- Then, select Next.
- 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.
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.
- 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.
- Then, from the Paste Special dialogue box, select the oval beside the Add option.
- Finally, click OK.
- 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.
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.
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.
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.
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.
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.