To convert text to number we can use error message that appears in the upper left corner of the cell, simple Copy-Paste special option or VALUE function.
This step by step tutorial will assist all levels of Excel users in learning how to convert text to number.
Convert Text to Number Using Error Message
We can easily recognize number formatted as text with a green triangle in the upper left corner of the cell. When we put the cursor on the triangle the error message will appear.
Figure 1. number stored as text
There are also other signs that number is formatted as text:
- When we select the numbers stored as text, the Status bar will display count of the cells. However, for numbers it will appear as sum and average of the cells
- The Number stored as the text has left alignment in the cell while the number has the right
- Numbers stored as text have the leading apostrophe in the formula bar
Figure 2. convert number to text
To convert string to number, follow the steps:
- Select the numbers formatted as text
- Click on the error message and choose Convert to Number
- Excel converts text to numbers in all selected cells
Figure 3. Convert text to number
Change Text to Number with Copy and Paste Special
Another way to convert text to numbers is to use Copy, Paste Special Excel option:
- Select the numbers stored as text with errors, right-mouse click and choose Copy
Figure 4. Copy numbers stored as text
- Right-mouse click again and choose Paste Special
- In Paste Special choose Values and Add and press OK
Figure 5. Change text to number
- As a result, Excel converts text to numbers
Figure 6. change the text to number with Copy, Paste Special
Convert String to Number with VALUE Function
Another way to change the text to a number is to use VALUE function:
- Select the cell C3 and insert the formula
=VALUE(B3) - Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.
Figure 7. How to convert text to number with VALUE function
The VALUE function has one argument, the number stored as text. When we copy the formula down to the other cells, Excel converts all text cells to number.
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment