Excel Error: The Number in This Cell is Formatted as Text (6 Fixes)

In this article, we will discuss the ‘The number in this cell is formatted as text or preceded by an apostrophe’ error in Excel. Sometimes, when we work with some number values in our Excel sheet, the numbers don’t sum up, don’t perform any mathematical operations, and produce errors. This can happen because the numbers may be formatted as text or preceded by an apostrophe. We will try to convert the text cells into numbers or remove the preceded apostrophe to fix this problem.

Why Does ‘The Number in This Cell is Formatted as Text or Preceded by an Apostrophe’ Error Occur?

When we use a preceding apostrophe before a number, this error occurs. Sometimes the values are stored as strings in our dataset. This can also cause the same problem.

How to Find a Cell That is Formatted as Text or Preceded by an Apostrophe

We can easily find a cell that is formatted as text or preceded by an apostrophe. If you see a green small triangular box at the top-right side of the cell, the cell may be formatted as text. But to be completely sure, we can use a formula. To do so, follow the steps below.

STEPS:

Select an adjacent cell and type the formula:

=ISNUMBER(B2)

Now, hit Enter to see the result.

Use the Fill Handle for all cells.

Here, only Cell B6 is stored as a number.

Excel Error: The Number in This Cell is Formatted as Text: 6 Suitable Solutions

To explain these solutions, we will use a dataset that contains information about working hours and the salary of some employees. Here, the number values are showing the error.

1. Convert Text to Number with Text to Columns

In this method, we will convert texts to numbers ‘Text to Columns feature. If we select a cell and put the cursor on the smart tag, it will display an error like below.

Let’s follow the steps to fix this problem.

STEPS:

Firstly, select all the cells of a single column. We have selected the cells of Column C.

Secondly, go to the Data tab and select Text to Columns. The Convert Text to Columns Wizard will appear.

Thirdly, click Finish from the Convert Text to Columns Wizard.

After that, the values of Column C will automatically convert to numbers and there will be no error.

Finally, do the same for Column D to see results like below.

2. Change Text to Number Using Smart Tags

We can use smart tags to convert the texts to numbers. This is the easiest and quickest solution. If you select a cell that contains any errors, the smart tags icon will appear automatically. We have highlighted it below.

Let’s pay attention to the steps below.

STEPS:

Select the error cells at first. We have selected Cell C5 to Cell D12.

Now, click on the smart tags icon. A drop-down menu will occur.
Select ‘Convert to Number’ from it.

Instantly, you will see results like below.

3. Apply Paste Special to Convert Text to Number or Remove the Preceded Apostrophe

We can use the paste special option of Excel to convert text to numbers. There are two ways to use the paste special option. We will paste values in these methods.

3.1 With Add Operation

In this first sub-method, we will use the add operation. Let’s follow the steps below to implement this technique.

STEPS:

In the first place, select any cell in your dataset. We have selected Cell C14.
Now, press Ctrl + C to copy the cell.

After that, select the cells from where you want to remove the error.

Next, select Paste from the Home tab and select Paste Special from the drop-down menu.

The Paste Special window will open. Then, select Values and Add from the Paste Special Click OK to proceed.

After clicking OK, it will display results like below.

To convert the Salary column into the desired format, select Column D.
Now, go to the Home tab and select Currency from the Number Format box.

Finally, you will see results like below.

3.2 With Multiply Operation

We can also use the multiply operation to fix this error. To do so, let’s follow the steps below.

STEPS:

In the beginning, select any cell in your dataset and write 1. We have selected Cell C14.

Now, press Ctrl + C to copy the cell.
After that, select the cells from where you want to remove the error.

Next, select Paste from the Home tab and select Paste Special from the drop-down menu. The Paste Special window will open.

Again, select Values and Multiply from the Paste Special window. Click OK to proceed.

After clicking OK, you will see results like below.

To convert the Salary column into Currency, select Column D.

Now, go to the Home tab and select Currency from the Number Format box.

In the end, you will see results like below.

4. Use of Value Function to Transform Text or a Cell Preceded by an Apostrophe to Number

We can use the VALUE function to convert text to numbers or remove the preceded apostrophe. The VALUE function converts a text string that represents a number into a number. We will use the same dataset here.

Let’s follow the steps to learn this solution.

STEPS:

First, insert a helper column like below.

Secondly, select cell D5 and type the formula:

=VALUE(C5)

Hit Enter to see the result.

After that, use the Fill Handle for the rest of the cells.

Now, select the values of the helper column and press Ctrl + C to copy and select Column C.

Next, go to the Home tab and select Paste Special.
Then, select Paste Values from the drop-down menu.

You will see no error in Column C.

Follow the same procedure and delete the helper column to see results like below.

Finally, you can change the Number Format to Currency to see results like below.

Read More: [Fixed!] VALUE Error in Excel

5. Insert Mathematical Operation to Change Text Cell to a Number

We can do the multiplication operation manually to convert text to numbers. We will need a helper column in this method also.

Let’s observe the steps below for this method.

STEPS:

Firstly, add an extra column like below.

Secondly, select cell D5 and type the formula:

=C5*1

Now, press Enter to see the result.

After that, use the Fill Handle to see results in all cells.

Next, delete the Helper column.

Repeat the steps for Column D and you will see results like below.

Lastly, change the Number Format to Currency to see the desired result.

6. Apply VBA to Convert Text to Number

We can also apply VBA to transform a text that represents a number into a number. Just follow the steps below to know this process.

STEPS:

In the beginning, go to the Developer tab and select Visual Basic. The Visual Basic window will appear.

Now, go to Insert and select Module.
Then, type the code in the Module window.

Sub Convert_to_Number()
With Worksheets("VBA").Range("C:C")
.NumberFormat = "General"
.Value = .Value
End With
With Worksheets("VBA").Range("D:D")
.NumberFormat = "General"
.Value = .Value
End With
End Sub

Here, we have selected Column C using Range. Then, changed the number format to general. We have repeated the same thing for Column D.

Press Ctrl + S to save the code.
After that, select Macros from the Developer tab. The Macro window will appear.

Select the code from the Macro window and Run it.

Finally, you will see results like below.

How to Ignore ‘The Number in This Cell is Formatted as Text or Preceded by an Apostrophe’ Error in Excel

Sometimes, we need to keep the cell as it is. So, we need to ignore the error. To ignore any error, select a cell that has an error and then select the smart tags option. You will find the ‘Ignore Error’ option in the drop-down menu.

Download Practice Book

Download the practice book here.

Number in Cell is Formatted as Text.xlsm

Conclusion

We have discussed 6 easy solutions to the error of a cell that is formatted as a text or preceded by an apostrophe. I hope these solutions will help you to fix your problem. Furthermore, you can also download the practice book to exercise. Last of all, if you have any queries or suggestions, feel free to ask in the comment section.

<< Go Back To Errors in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel Error: The Number in This Cell is Formatted as Text (6 Fixes)

Why Does ‘The Number in This Cell is Formatted as Text or Preceded by an Apostrophe’ Error Occur?

How to Find a Cell That is Formatted as Text or Preceded by an Apostrophe

Excel Error: The Number in This Cell is Formatted as Text: 6 Suitable Solutions

1. Convert Text to Number with Text to Columns

2. Change Text to Number Using Smart Tags

3. Apply Paste Special to Convert Text to Number or Remove the Preceded Apostrophe

3.1 With Add Operation

3.2 With Multiply Operation

4. Use of Value Function to Transform Text or a Cell Preceded by an Apostrophe to Number

5. Insert Mathematical Operation to Change Text Cell to a Number

6. Apply VBA to Convert Text to Number

How to Ignore ‘The Number in This Cell is Formatted as Text or Preceded by an Apostrophe’ Error in Excel

Conclusion

Related Articles

Leave a reply Cancel reply