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.


Download Practice Book

Download the practice book here.


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.


6 Solutions to the Excel Error: The Number in This Cell is Formatted as Text or Preceded by an Apostrophe

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.

Convert Text to Number with Text to Columns


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 the error like below.

Convert Text to Number with Text to Columns

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.

Convert Text to Number with Text to Columns

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

Convert Text to Number with Text to Columns

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

Convert Text to Number with Text to Columns

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

Convert Text to Number with Text to Columns

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

Convert Text to Number with Text to Columns

Related Content: How to Fix #REF! Error in Excel (6 Solutions)


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.

Change Text to Number Using Smart Tags

Let’s pay attention to the steps below.

STEPS:

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

Change Text to Number Using Smart Tags

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

Change Text to Number Using Smart Tags

  • Instantly, you will see results like below.

Change Text to Number Using Smart Tags

Related Content: Excel VBA: Turn Off the “On Error Resume Next”


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.

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

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

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

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

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

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

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

  • After clicking OK, it will display results like below.

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

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

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

  • Finally, you will see results like below.

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


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.

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

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

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

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

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

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

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

  • After clicking OK, you will see results like below.

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

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

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

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

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

  • In the end, you will see results like below.

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

Read More: Errors in Excel and Their Meaning (15 Different Errors)


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.

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

  • Secondly, select Cell D5 and type the formula:
=VALUE(C5)

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

  • Hit Enter to see the result.

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

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

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

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

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

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

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

  • You will see no error in Column C.

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

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

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

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

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

Read More: How to Remove Value Error in Excel (4 Quick Methods)


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.

Related Content: Reasons and Corrections of NAME Error in Excel (10 Examples)


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

Read More: On Error Resume Next: Handling Error in Excel VBA


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.


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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo