How to Fix All Numbers Stored as Text in Excel – 6 Easy Methods

This is the sample dataset.

Method 1 – Using the Flash Fill Feature

Steps:

• Add a new column.
• Enter the three first numbers from the Age column in the new column.

• Select the three first cells of the new column.
• Drag down the Fill Handle.
• Select Auto Fill Options.

• In Auto Fill Options select Flash Fill.

• This is the output.

Note: The Flash Fill feature aligns data to the right. Here, data was centered.

Read More: Excel Convert to Number Entire Column

Method 2 – Utilizing a Smart Tag to Fix All Numbers Stored as Text in Excel

This is the sample dataset.

Steps:

• Select all cells that show the Smart Tag.
• Click the error button.

• Select Convert to Number.

• This is the output.

Read More: How to Fix Convert to Number Error in Excel

Method 3 – Changing the Cell Format

Steps:

• Select all the cells in the Age column.
• In the Home tab, select Number Format and choose General.

• This is the output.

Read More: How to Convert Bulk Text to Number in Excel

Method 4 – Using the Paste Special Option to Fix All Numbers Stored as Text in Excel

This is the sample dataset.

Steps:

• Press CTRL+C  to copy all the cells in the column Age

• Select F5 in the new column (Paste Special).
• In the Home tab, select Paste and choose Paste Special.

In the Paste Special window:

• Select Values.
• Click OK.

• This is the output.

Read More: How to Convert Green Triangle to Number in Excel

Method 5 – Utilizing the Multiply Option in the Paste Special Dialog Box

This is the sample dataset.

Steps:

• Select an empty cell (F5) and enter 1.

• Copy that cell.
• Select all the cells in the column Age.
• In the Home, choose Paste.
• Select Paste Special.

In the Paste Special window:

• Select Multiply.
• Click OK.

• This is the output.

Method 6 – Applying the VALUE Function to Fix All Numbers Stored as Text in Excel

This is the sample dataset.

Steps:

• Select E5 (the first cell of the new empty column).

• Enter the following formula.

Here, D5 is the first cell of the column Age.

• Drag down the Fill Handle.

• This is the output.

Read More: How to Convert Text with Spaces to Number in Excel

Practice Section

Practice here.

Download Practice Workbook

Related Articles

• How to Convert Alphabet to Number in Excel
• How Excel Formulas Convert Text to Number

<< Go Back to Convert Text to Number in Excel | Learn Excel