How to Add Leading Apostrophe In Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

dataset


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),

="'"&C6

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

formula

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

add leading apostrophe

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.

add leading apostrophe


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,

=CHAR(39)&C6

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.

char

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

char function

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.

add leading apostrophe


3. CONCAT Function

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

=CONCAT("'",C6)

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

concat

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

add leading apostrophe

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.

add leading apostrophe

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.

vba

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.

code

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.

opening macro

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

macro window

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.

add leading apostrophe

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.

error sign

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

Ignore error

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.

remove error


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.

immediate 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.

add leading apostrophe


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.

Prantick Bala
Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

4 Comments
  1. Method number 4 works, but as soon as I enter a value into the cell the apostrophe goes away completely? Why is this? Thank you!

    • Hello, CHRIS.
      Thank you for your comment. Actually, with the help of method 4, you are converting numbers into text. But when you re-entered any new value then the cell will hold that value excluding the apostrophe. Basically, the past value along with the apostrophe completely had gone away. So, if you want to keep the apostrophe then you should select that cell (containing new value) and run the Macros again. Then, you will see the apostrophe again with the new value.

  2. Reply
    Syed Anwar Hussain Feb 21, 2023 at 1:09 PM

    Hey, you saved lot of my time. Thanks a ton.

    • Dear Syed Anwar Hussain,

      Thanks for your appreciation.

      Regards
      Shamima | Project Manager | ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo