Sometimes, while working on Microsoft Excel, we might find all numbers are stored as text. This will hamper our actions in a lot of ways. So, we might need to fix this problem without wasting any time. This article demonstrates how to fix all number stored as text in Excel with 6 feasible solutions.
Watch Video – Fix All Number Stored as Text in Excel
How to Fix All Number Stored as Text in Excel: 6 Solutions
Let’s assume you have a dataset where you have a List of Employees along with their Name and Age. But you have a problem that the data in the Age column is stored as text in Excel. As Excel stores them as text, they have an alignment to the left of the cell. At this point, you want to fix this.
Now, to fix all these numbers stored as texts you can follow any of the following 6 solutions.
We have used Microsoft Excel 365 version for this article, you can use any other versions according to your convenience.
1. Using Flash Fill Feature
In this method, we will fix the issue using the Flash Fill feature in Excel. This method is very fast and also convenient. Now, to do so follow the below steps.
Steps:
- First, add a new column.
- Next, write down the first three data in the Age column in the new column.
- After that, select the first three cells of the new column.
- Then, drag the Fill Handle for the remaining cells of the column.
- At this point, select the Auto Fill Options.
- Then, from the Auto Fill Options select the Flash Fill option.
- Eventually, you will have your output as shown in the below screenshot.
Note: After using the Flash Fill feature, you will have the output aligned to the right of the cell. In this case, I changed the alignment to the middle. I will do this for the rest of the methods also.
Read More: Excel Convert to Number Entire Column
2. Utilizing Smart Tag to Fix All Number Stored as Text in Excel
Sometimes, when you double-click on a cell containing numbers in the disguise of the texts, a Smart Tag will pop up as a green triangle in the top left corner of the cell. You can use this Smart Tag to fix all numbers stored as text in Excel. Now, to do so follow the steps below.
Steps:
- First, select all the cells that show the Smart Tag.
- Then, click on the error button.
- Now, select the Convert to Number option.
- Finally, you will finish fixing your problem.
Read More: How to Fix Convert to Number Error in Excel
3. Applying Changes to Cells Format
Another way to fix all numbers stored as text in Excel is to change the cell format. You can change the format of the cells in various ways. In this case, we will see the fastest way. Now, to do so follow the steps below.
Steps:
- At the very beginning, select all the cells of the Age column.
- Then, from the Number Format in the Home tab, select General.
- Eventually, you will have your output as shown in the below screenshot.
Read More: How to Convert Bulk Text to Number in Excel
4. Using Paste Special Option to Fix All Number Stored as Text in Excel
In this method, we will use the Paste Special feature in Excel to fix all numbers stored as text in Excel. Now, if you want to do so, you can follow the below steps.
Steps:
- First, copy all the cells in column Age by pressing CTRL+C.
- Then, select cell F5 in the new empty column named Paste Special.
- Now, go to Paste options from the Home tab.
- After that, select Paste Special.
At this point, a Paste Special box will pop up.
- Next, select Values.
- Consequently, click on OK.
- Lastly, you will have your output as shown in the below screenshot.
Read More: How to Convert Green Triangle to Number in Excel
5. Utilizing Multiply Option of Paste Special Dialog Box
One more way to fix all numbers stored as text in Excel is to use the Multiply option in the Paste Special feature. Now, to do so follow the below steps.
Steps:
- First, select an empty cell F5 and put the number 1 in it.
- Then, copy the cell containing the number 1.
- After that, select all the cells of the column Age.
- Now, go to Paste options from the Home tab.
- After that, select Paste Special.
At this point, the Paste Special window will pop up.
- Next, select Multiply.
- Consequently, click OK.
- Eventually, you will have your output as shown in the below screenshot.
6. Applying VALUE Function to Fix All Number Stored as Text in Excel
In this method, we will use the VALUE function to fix all numbers stored as text in Excel. Now, to do so follow the below steps.
Steps:
- First, select cell E5.
In this case, cell E5 is the first cell of a new empty column.
- Then, insert the following formula into cell E5.
=VALUE(D5)Here, cell D5 is the first cell of the column Age.
- At this point, drag the Fill Handle for the remaining cells of the column Age.
- Finally, you will have your output in the new column as shown in the below screenshot.
Read More: How to Convert Text with Spaces to Number in Excel
Practice Section
For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.
Conclusion
Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below.
