Numbers that are stored as text can cause unexpected results, especially when you use these cells in Excel functions such as SUM and AVERAGE because these functions ignore cells that have text values in them. So you need to convert numbers stored as text to numbers.
How do I change my number stored as text to number?
Select the Excel cells and then click the trace error button and select the convert to numbers option. Or, follow the methods below if that button is not available.
Convert Numbers stored as Text to Numbers in Excel
You can follow any one of these methods below to convert numbers stored as text to numbers in Microsoft Excel:
- Using the Text to column button
- Using Value function
- Changing the format
- Use Paste Special and multiply
1] Using the Text to column button
Select the column or select one or more cells, ensure that the cells you have selected are in the same column, or else the process won’t work.
Then click the Data tab and click the Text to column button.
A Convert text to column wizard dialog box appears.
Select the Delimited option, then click Next.
Select Tab as the delimiter, then click Next.
Select General as the column data format, then click Finish.
2] Using Value function
You can use the Value function to return the numeric value of the text.
Select a new cell in a different column.
Type the formula =Value() and inside the parentheses, type a cell reference that contains text stored as numbers. In this example, it’s cell A2.
Press Enter.
Now place the cursor at the lower right corner of the cell and drag the fill handle down to fill the formula for the other cells.
Then copy and paste the new values to the original cell column.
To copy and paste the values to the Original column, select the cells with the new formula. Press CTRL + C. Then click the first cell of the original column. Then on the Home tab, click the arrow below Paste, and click Paste special.
On the Paste Special dialog box, click Values.
3] Changing the format
Select a cell or cells.
Then Press Ctrl + 1 button to open a Format Cells dialog box.
Then select any format.
4] Use Paste special and multiply
If you are converting more than one column of text to numbers, this is an excellent method to use.
Select a blank cell and type 1 into it.
Then press Press CTRL + C to copy the cell.
Then select the cells stored as text.
On the Home tab, click the arrow below Paste, and then click Paste Special.
On the Paste Special dialog box, click Multiply.
Then click OK.
Microsoft Excel multiplies each cell by 1, and in doing so, converts the text to numbers.
We hope this tutorial helps you understand how to convert numbers stored as text to numbers in Excel.
Why is my number stored as text in Excel?
Excel doesn’t convert numbers written in text into digits if you import data from another source because of the default setting. That being said, even if you copy data from the internet and paste it into the Excel spreadsheet, you can find numbers in the text. You can follow the aforementioned steps to convert the numbers from text to digits.
How do I ignore all numbers stored as text in Excel?
To ignore all numbers stored as text, you need to open the Data tab and select the Text to Columns option. Then, select the Delimited option from the Convert Text to Columns wizard. Remove the tick from all the checkboxes and select the General option in the Column data format panel. Finally, click the Finish button to make the conversion happen.
If you have questions about the tutorial, let us know in the comments.
