How to Fix “The number in this cell is formatted as text or preceded by an apostrophe” Excel?

While working on an important spreadsheet, you might see a small green triangle in a cell. Hovering over it shows the message: “The number in this cell is formatted as text or preceded by an apostrophe.” This warning typically occurs when trying to copy and paste links from another worksheet. Let’s explore the best ways to fix this error effectively.

What Causes Text or Preceded by an Apostrophe Excel Error?

Excel treats numbers formatted as text differently. This usually happens when:

1. Numbers hold a leading apostrophe (‘123)
2. Data is imported from another source
3. Cells have been formatted as Text instead of Number
4. Extra spaces or non-printable characters are present.
5. Corrupted file.

After knowing the major factors, it’s time to solve the apostrophe Excel error with the best tricks.

How to Remove the Number in This Cell Is Formatted as Text or Preceded by an Apostrophe?

Follow the below tweaks to troubleshoot this error message.

Solution 1- Use ‘Convert to Number’ Feature

The very first method you can try is converting text to a number using the ‘Convert to Number’ option. This feature will ultimately help you to change the data that has been entered with an apostrophe.

Here’s how you can do so:

• Firstly, select the cell(s) you need to change to number format.
• Next, you will see a yellow diamond exclamation symbol near the selection.
• After that, click on the dropdown >> choose ‘Convert to Number’ option.

• Finally, you will get your text converted to a number.

Also Read: Fixed- Excel “DataFormat.Error We Couldn’t Convert to Number” Error!

Solution 2- Remove Apostrophe

Another straightforward way that can be tried is to remove the apostrophe manually. Sometimes numbers have an apostrophe at the start, removing it can solve the problem easily.

• Simply double-click the cell.
• Then, delete the apostrophe manually.
• Hit the Enter to confirm.

This forces Excel to treat the value as a number.

Solution 3- Changing Cell Format

You can even fix the number in this cell is formatted as text or preceded by an apostrophe Excel error by changing the cell format. This will immediately convert the text to a number in an Excel spreadsheet.

To do so, follow the steps below carefully:

• Select the affected cell(s).
• Navigate to the Home tab.

• Under the Number group, you have to choose Number from a format dropdown.

• Press F2 >> hit Enter for each cell to refresh the data.

Solution 4- Using Paste Special

Many users have found this method useful in their cases. So, if you also want to overcome this hectic situation, then use the Paste Special feature of Excel.

• First of all, enter 1 in a blank cell.
• Then, copy it (Ctrl+C).
• Choose the problematic cells.
• After that, right-click & choose the Paste Special.

• Next, you have to click on Multiply >> OK.

• This forces Excel to reformat the values as numbers.

Also Read: Excel “External Table Is Not in The Expected Format.” Error!

Use Automated Tool- Excel Repair Software

You may also receive an apostrophe Excel error due to workbook corruption. In such a case, you can try the expert’s recommended solution, i.e, Excel Repair Software. It can repair multiple files at once & restore every component in the preferred location

This tool supports the entire Excel version & free demo version. It is compatible with both Windows & Mac operating systems.

Try Now       Buy Now

* Free version of the product only previews recoverable data.

Related FAQs:

What Is the Number Preceded by Apostrophe in Excel?

Leading apostrophes forces in excel to treat the spreadsheet cell's contents as a text value. Even if the cell holds a number or date, Excel will treat it as text.

Why Put an Apostrophe Before a Number in Excel?

In Microsoft Excel, putting an apostrophe before a number aids a unique function. It forces the Excel to treat the number as text.

How Do I Remove Preceding Text in Excel?

If you want to remove preceding text in Excel: 1. Press Ctrl + H keys together to open the Find & Replace dialog. 2. Under Find what box, you have to enter one of the below combinations:

• To remove text after a certain character, type the character followed by an asterisk (char*).
• To eliminate text before a given character, type the character preceded by an asterisk (*char).

How to Use Special Characters in Excel Formula?

To use special characters in Excel formula, you have to click where you need to insert the special character. Navigate to Insert > Symbol option > More Symbols. Click on the Special Characters. Then, double-click on the character you want to insert.

Summing Up

Addressing the error “the number in this cell is formatted as text or preceded by an apostrophe” is a dynamic talent for anyone working with worksheets. Nevertheless, by understanding the causes and applying the right techniques like Convert to Number, Paste Special, or Text-to-Columns, you can fix it instantly.

Keeping your Excel data clean from the start will save time and prevent errors in the future.