Why does Excel add leading apostrophes to some of my text cells?

Question

I have a worksheet containing, among other things, a table object. All data in the worksheet was added by me manually, including the data and column headers in the table.

I just noticed that in 13 of the 16 table column headers, Excel has added a leading apostrophe to the header. In the other 3, the column header does not have the leading apostrophe. Investigating further, I found 5 more cells (outside the table) where Excel has added a leading apostrophe to the text content.

All other cells do not have the leading apostrophe. With the exception of the table totals row, all other cells either contain text or are empty.

When I try to remove the leading apostrophe from the cell contents, it's immediately re-added when I hit Enter. When I type new content for these cells, the apostrophe is added every time. When I enter numerical data (recognized and right-outlined), and then change the content to text again, the apostrophe re-appears.

In the otherwise identical cells that do not have an apostrophe, this behavior does not occur. Entering text results in text content without apostrophe.

All cells have General formatting.

What's causing this apostrophe to appear, and how do I get rid of it?

Edit: It's something in the cell formatting, because I can make the cell behave normally by erasing its formatting. But there is nothing set in the Cell Properties, and like I said, number formatting is set to General.

Answer 1

The "why" is simple: those apostrophes are present in EVERY cell containing or being treated as text. Excel does not normally display them, either in the formula editor or in the grid's display of the contents, but they exist.

The places they show are places that Excel has been coerced into displaying them. One way is the obvious, that of actually typing one before completing one's entry or edit. However, other mechanisms make them visible as well.

Corresponding characters exist for the other basic alignment formattings, "center" (^) and "right" ("). Again, not normally shown to us humans.

One way I know of to get one is to do the following:

1. Format a cell as TEXT.
2. Type =746 in it. (Entering a number makes a difference.)
3. Edit the cell to include an apostrophe after the = before the 7.
4. Press Enter.

The cell will display =746 on the grid, but in the formula editor, you will see '='746. Note the new apostrophe before the =.

This is a situation in which the exact material being handled in the circumstances ends up with Excel showing the apostrophe. Edit the cell, removing the ' AFTER the = and to replace 746 with abc (so placing clearly textual character in place of the numeric characters) and press Enter. Even though the cell is formatted as text and contains what is clearly text, Excel will still show the leading apostrophe. Once shown, it does not seem to stop displaying them.

Even copying text from elsewhere and pasting values does not. Once a cell has the displaying of the apostrophe turned on, it seems to stay on.

Importantly, it is simply displayed. It has no effect on calculations. For instance, LEN() will show 5 in the above example with 746, not 6.

You can often, though also often not, easily see the apostrophe being present during much data entry if you watch the entry closely. A leading apostrophe will often be visible during entry, though often it flickers. And then NOT present in the displayed cell contents, either on the grid or in the formula editor.

They are not harmful in any way, just disturbing. Probably why no one seems to have delved much into how to cause them, only to remove them. Or more properly, stop displaying them.

(Since you — pointedly — disdain the idea of anything about how to stop their display, I shall not mention one easy way. Perhaps it is one of the three ways harrymc links to.)

There isn't much lying about regarding why Excel comes to show some, though the little I've read suggests an action is taken that triggers Excel (what is called "coercion" when done on purpose to make Excel do things it does not normally do). Not necessarily a wrongful action at all, just an action. (The following does NOT happen. It is just a quick pretend example of a series of actions as I do not teach this subject and have a real such example in my pocket.) For instance, cells with a General format have entries, some numeric. One creates a Table using them as headers. Excel converts whatever is in them to text. Any purely numeric cell values start having their formatting characters shown (leading apostrophe in your case). (Again, just a pretend example that actually does not happen.)

To summarize, they are always present, just not shown.

(A little more for the "746" example above: If you copy the formatting of the cell that shows the leading apostrophe to another cell (so formats are identical), then paste the value of the copied "746" cell ('='746) to it, the leading apostrophe is gone. Well, that happens, things like that anyway, with Excel. Then recalcing or editing the cell causes it to revert to how you expected it to be. But in this case it does not. So the exact FORM of things is not the issue, but rather the sequence of actions that led to that form, they typing steps and so on. Odd, no?)

Answer 2

Leading apostrophes force excel to treat the cell’s contents as a text value. Even if the cell contains a number or date, Excel will treat it as text. The apostrophe can only be seen in the Formula bar when selecting the cell, and otherwise stays invisible.

This way, one may avoid entering a value like Jan-01 and Excel converting it to a date and formatting it according to the global date format.

You may remove these apostrophes, as explained for example in the article How to Remove Apostrophe in Excel (3 Easy Ways), but better ensure in that case that Excel still treats these cells as text.

Answer 3

I cannot edit the existing answer, nor comment, so I'll add this in response to the second comment as it could help you, and others.

I looked at those methods in the other answer's link and they all revolve around the idea of doing an operation that forces ("coerces" to use the usual term) Excel to evaluate the contents for data type and realize it is displaying them wrongly, then fix that. It is a method that works about 95% of the time I've tried it in situations in which it appeared that should be applicable. The remaining times, well, they never were clear. And usually those situations address more of a "was formatted as text, changed the format, now need to coerce the display to suit me" kind of thing than what you describe.

But I tried two of them and they did not work on the created situation I mentioned in my answer (the "746" situation... which, by the way, I cannot create today, not the way it worked yesterday, though I CAN create it). And as you say, why would they anyway? The situation is materially different from simply displaying something wrongly on the grid.

However, what worked in THIS situation, and maybe yours, yesterday and today, was for me to:

1. Format some other cell for TEXT.
2. Copy the offending cell, then paste values into the newly formatted cell. (At this point the offending leading apostrophe is no longer showing, in the newly formatted cell.)
3. Copy the newly formatted and pasted into cell, then paste, NOT paste values, back into the original cell.

All text, the whole way, not numbers that were formatted as text, then reformatted but still displayed as text (aligning to the left edge and so on).

However, after those steps, my leading apostrophe no longer showed in the formula editor when examining the original cell.

Perhaps such a method would work on your material, text to begin with, text to end with. For row material, even in a very used place, one could insert a row, do it, then delete the row, though a tighter idea would be to just insert a blank sheet to do the middle step into and out of, deleting the sheet after doing it.

Maybe someone who can edit will transfer this into my answer or write it up as a comment. Experience says someone will just delete it, but maybe humanity has turned a corner and they'll be helpful first.

Answer 4

I don’t have anything to add just sharing similar experience I usually paste similar &&& formula from my Excel to notepad without the quote appearing. Today I paste it and quotes appear in some line,but not the other. I searched internet first and find this thread, I check back to my formula and find out I accidentally paste the line break while adjusting my formula

https://answers.microsoft.com/en-us/msoffice/forum/all/why-is-excel-adding-quotes-when-i-copy-a-cell/9633ca38-81b3-4c0f-981a-05e33c4ba419