Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Fix All Number Stored as Text in Excel (6 Easy Solutions)

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.


Download Practice Workbook

You can download the practice workbook from the link below.


6 Solutions to Fix All Number Stored as Text in Excel

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.

excel number stored as text fix all dataset

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.

Using Flash Fill

Steps:

  • First, add a new column.
  • Next, write down the first three data in the Age column in the new column.

Using Flash Fill Feature

  • 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.

Using Flash Fill Feature

  • Then, from the Auto Fill Options select the Flash Fill option.

Using Flash Fill Feature

  • Eventually, you will have your output as shown in the below screenshot.

Using Flash Fill Feature

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.


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.

Utilizing Smart Tag to Fix All Number Stored as Text in Excel

Steps:

  • First, select all the cells that show the Smart Tag.
  • Then, click on the error button.

Utilizing Smart Tag to Fix All Number Stored as Text in Excel

  • Now, select the Convert to Number option.

Utilizing Smart Tag to Fix All Number Stored as Text in Excel

  • Finally, you will finish fixing your problem.

Utilizing Smart Tag to Fix All Number Stored as Text in Excel

Read More: How to Fix Convert to Number Error in Excel (6 Methods)


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.

Applying Changes to the Cells Format

Steps:

  • At the very beginning, select all the cells of the Age column.
  • Then, from the Number Format in the Home tab, select General.

Applying Changes to the Cells Format

  • Eventually, you will have your output as shown in the below screenshot.

Applying Changes to the Cells Format


Similar Readings


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.

Using Paste Special Option to Fix All Number Stored as Text in Excel

Steps:

  • First, copy all the cells in column Age by pressing CTRL+C.

Using Paste Special Option to Fix All Number Stored as Text in Excel

  • 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.

Using Paste Special Option to Fix All Number Stored as Text in Excel

At this point, a Paste Special box will pop up.

  • Next, select Values.
  • Consequently, click on OK.

Using Paste Special Option to Fix All Number Stored as Text in Excel

  • Lastly, you will have your output as shown in the below screenshot.

Using Paste Special Option to Fix All Number Stored as Text 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.

Utilizing Paste Special and Multiply

Steps:

  • First, select an empty cell F5 and put the number 1 in it.

Utilizing Paste Special and Multiply

  • 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.

Utilizing Paste Special and Multiply

At this point, the Paste Special window will pop up.

  • Next, select Multiply.
  • Consequently, click OK.

Utilizing Paste Special and Multiply

  • Eventually, you will have your output as shown in the below screenshot.

Utilizing Paste Special and Multiply


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.

Applying VALUE Function to Fix All Number Stored as Text in Excel

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.

Applying VALUE Function to Fix All Number Stored as Text in Excel

  • Finally, you will have your output in the new column as shown in the below screenshot.

Applying VALUE Function to Fix All Number Stored as Text in Excel

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


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.

Practice 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. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Sowmik Chowdhury

Sowmik Chowdhury

Hello! Welcome to my Profile. I , Sowmik Chowdhury, graduated from the Bangladesh University of Engineering and Technology from the Department of Naval Architecture and Marine Engineering. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this..

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo