Excel cells sometimes doesn't show the '

[HotWheel]

Ok so im a Hot Wheels collector and I track everything in excel that I own and for the past few months I have been fighting an issue. Some cells won't show the ' when the data in the cell starts with '18 abc efg etc. The cells are formated the same but some cells will show the ' in the beginning. Sometimes copying the cell that works and pasting special resolves it. Other times it doesn't. Does any know how I can make the cells always show the '? It shows it in the Formula bar but not in the cellHow it should be displayed.jpgBroken.jpg

[TMS]

A single quote in front of a value in a cell is normally processed by Excel as "treat this cell as text", even if te content IS text.

I can't find an option to switch this off, though that's not to say it doesn't exist.

You can, however, get around it by typing the single quote mark twice OR press space and then the single quote mark. The space option will offset the text so typing the single quite mark twice is probably your best bet.

The only other way is, if you have a cell that displays as you wish with only one single quote mark, copy that cell and edit it.

Unfortunately, without a sample file, I am unable to assess, compare and contrast the different cell formats that you currently have.

[HotWheel]

both the things you suggested worked fine and attached is a copy of a couple cells that act differently if your curious. If it can be figured out that would be great cause i'm afraid at some point i may have to filter data and having a space or extra ' may jack me up.submission.xlsx

[josephteh]

Just wonder, why can't you to show the year in full, especially when '76 Greenwood Corvette refers to the year 1976 and '08 Ford Focus refers to the year 2008, i.e. in different centuries?

[HotWheel]

Just wonder, why can't you to show the year in full, especially when '76 Greenwood Corvette refers to the year 1976 and '08 Ford Focus refers to the year 2008, i.e. in different centuries?

Because that's how Mattel has them listed as. While I agree it would be easier it's now how they list them.

[josephteh]

Another method is to use custom cell formatting for those specific cells where you want the ' to appear: '@.