How to Add Leading Apostrophe In Excel (5 Ways)

You can convert a number into text by adding a leading apostrophe. From this Article you’ll get to know how you can add leading apostrophe in Excel.

Suppose, we have a dataset where different products have different delivery codes in Column C. Now, we’ll add leading apostrophes to the cells of column C.

Download Practice Workbook

5 Ways to Add Leading Apostrophe in Excel

1. Add Leading Apostrophe with a simple formula

You can add a leading apostrophe to a cell by applying the following formula in an empty cell (D6),

Here, the formula will add an apostrophe (‘) in the beginning of the cell C6’s entry and will give the return in cell D6.

Press ENTER and you will see an apostrophe has been added in front of cell C6’s entry.

Drag cell D6 to the end of your dataset to apply the same formula for all other cells of Column C. As a result you will get the data of column C with leading apostrophes in column D.

2. CHAR Function to Add Leading Apostrophe

You can also add a leading Apostrophe to a cell by using the CHAR function. Type the following formula in cell D6,

Here, 39 is the character code of the apostrophe. The formula will add an apostrophe in front of cell C6’s data and will give the return in cell D6.

Press ENTER and you will see an apostrophe has been added in front of the cell C6’s data.

Drag the cell D6 to the end of your dataset and You will get the data of column C with leading apostrophes in column D.

3. CONCAT Function

Another way to add leading apostrophes is using the CONCAT function. Type the following formula in cell D6,

Here the formula will add an apostrophe (‘) in front of the data of cell C6 and give the result in cell D6.

Press ENTER and you will see in cell D6 that an apostrophe has been added in front of the cell C6’s data.

Drag the cell D6 to the end of your dataset and You will get the data of column C with leading apostrophes in column D.

For your information, you can also use the CONCATENATE function to do the same task.

4. Add Leading Apostrophe with VBA code

If you have a very long dataset, the process of adding leading apostrophes with the previous methods can be tiresome. But with the help of the Microsoft Visual Basic Applications (VBA), you can easily add leading apostrophes with a couple of clicks.

First, we need to set a macro to add leading apostrophes. Press ALT+F11 to open the VBA window. From the Project panel of this window, right click on the sheet name and expand Insert. After that, click on Module to open the Module(Code) window.

In the Module(Code) window insert the following code,

Sub AddAnApostrophe()

For Each cell In Selection
    cell.Value = "'" & cell.Value
Next cell

End Sub

Here the code will add an apostrophe in each selected cell after running the macro.

After inserting the code, close the VBA window. Now, select the cells where you want to add leading apostrophes and go to the View tab and click on Macros.

It will open a window named Macro. Select AddAnApostrophe from the Macro Name box and click on Run.

As a result, all of your selected cells will have a leading apostrophe. But if you observe the cells you can see no apostrophe is shown on those cells and there is an error sign in every cell. Actually, the apostrophe is hidden and the error sign is shown because the data of each cell is a number but it is now converted into a text. Excel treats this as an error. However, you can see the hidden apostrophe from the formula bar, and with a few clicks you can remove the error sign.

If you click on any cell and look into the formula bar you can see there is a leading apostrophe. To remove the error sign, click on the Error sign box shown on the left of your selected cell.

After clicking on the Error sign box a dropdown menu will appear. Select Ignore Error from this menu.

Now you can see there is no error sign in that cell. You can remove the error sign from all of the cells in a similar manner.

5. Immediate Window to Add Leading Apostrophe

Another easy way to add leading Apostrophe is using the Immediate window of VBA. Press ALT+F11 to open the VBA window and then press CTRL+G to open the Immediate window. Now insert the following code in this Immediate window and press ENTER.

for each v in range("C6:C11") : v.value = "'" & v.value : next.

The code will add a leading apostrophe in every cell in the range C6:C11. At last, close the VBA window.

As a result, each of your cells will have a leading apostrophe. If you want to remove the error sign from the cells, follow the steps which are mentioned in the previous method.

Conclusion

You can add leading apostrophe in Excel by following any of these five ways though using VBA (method 4) is more convenient. If you have any confusion please leave a comment.