The Hidden Formatting Character
In Microsoft Excel, there is a long-standing, universally known trick: if you type a single leading apostrophe (') before a number, Excel will instantly treat that number as plain text. This prevents Excel from deleting leading zeros (e.g., turning '00123 into 123) or converting long IDs into scientific notation. The apostrophe itself is automatically hidden from view in the spreadsheet grid, appearing only in the formula bar. However, this helpful feature becomes incredibly frustrating when you actually want the apostrophe to be visible in the cell—for instance, when typing coordinates, measurements (like 5’11”), or specific programming syntax. Excel automatically consumes the first apostrophe it sees and hides it.
How to Force Excel to Show the Leading Apostrophe
Because Excel’s core programming dictates that the very first apostrophe in a cell is a formatting command, not a character, you cannot simply type one apostrophe and expect it to show up.
Method 1: Type Two Apostrophes
The simplest workaround to defeat this automatic behavior is to feed Excel an extra character to consume.
1. Select the cell where you want to type your data.
2. Type two single apostrophes (''), followed by your data (e.g., ''00123).
3. Press Enter.
Excel will consume the first apostrophe and hide it, treating it as the “format as text” command. The second apostrophe will be treated as standard text and will remain fully visible in the cell.
How to Fix Apostrophes Removed During CSV Import
A more severe problem occurs when you import data from a CSV file or a database export. If the raw data contains leading apostrophes, Excel will automatically strip them out the moment you open the file, permanently destroying the original data formatting.
To stop this automatic stripping, you must import the data using Power Query, rather than just double-clicking the CSV file.
1. Open a completely blank Excel workbook.
2. Go to the Data tab on the ribbon.
3. Click Get Data > From File > From Text/CSV.
4. Select your file and click Import.
5. The preview window will appear. Do not click Load yet. Click the Transform Data button.
6. The Power Query Editor will open. Look at the columns containing your apostrophes. Power Query usually attempts to auto-detect data types and might have already stripped them.
7. In the “Applied Steps” pane on the right, look for a step called Changed Type. Click the X next to it to delete this automated step.
8. Your data will revert to raw text, and your leading apostrophes will reappear.
9. Click Close & Load in the top-left corner. The data will be pushed into your spreadsheet exactly as it was written in the source file, with all apostrophes perfectly preserved.