Learn two simple methods, manual and automatic, to quickly add an apostrophe before any number in Microsoft Excel.
It’s not often, but if you work with Excel a lot, there will be instances where you might want to add an apostrophe to a number or a column of numbers. For example, to preserve leading zeros (e.g., in zip codes or ID numbers), store 16+ digits (e.g., credit card numbers) without rounding off or converting to scientific notation, to treat numbers as text identifiers, etc.
Whatever your specific reasons and use cases are, when needed, you can easily add an apostrophe before a number in two ways, manually and automatically. The manual method is useful if you have a limited number of cells to edit. However, if you already have a big column of numbers, the automatic method is helpful.
In this quick and easy guide, I will show these two methods to add an apostrophe before a number in Excel. Let’s get started.
Before You Start
- Once you add an apostrophe before a number, Excel will treat it as text, not a number.
Manually Add an Apostrophe Before a Number
If you want to edit a few cells to prefix an apostrophe to a number, you can do it manually. Don’t need any fancy formulas. All you have to do is double-click the cell, place the text cursor at the start of the number (tip: press the ‘Home’ key), and press the apostrophe key on your keyboard.
Add an Apostrophe Before Number Automatically (for an entire column)
If you have a large column of numbers, you can use a simple formula to prefix an apostrophe automatically. Here’s how to do it in simple steps.
Note: In this tutorial, I assume that your numbers are in Column A, starting from cell A1. If it is different, then modify the formula accordingly.
First, click an empty cell in a new column where you want to type the formula. Next, type the following formula and press Enter. This action instantly takes the number of the A1 cell, adds an apostrophe before it, and saves it to the new cell.
= "'" & A1
Now that you have the first number with a leading apostrophe, you can apply the same formula to all the other numbers. To do that, simply click the new cell containing the formula. Next, click and drag the small square dot at the bottom-right corner of the cell (this is called the Fill Handle) to the length of the original numbers column.
This action will automatically apply the formula to each corresponding cell and instantly add an apostrophe to every number in the list.
Note: When you hover your mouse over the Fill Handle, the cursor will change to a thin plus sign (+).
Note 2: Keep in mind that Excel’s number precision is limited to 15 digits. If your original numbers were 16+ digits, Excel has already truncated them (changed the end to zeros), and this formula cannot recover the original number.
—
That is all. It is that simple to add a leading apostrophe to a number or a column of numbers in Excel, either manually or automatically using a formula. If you have any questions or need help, comment below. I’ll be happy to assist.
