How to Add Leading Apostrophe In Excel (4 Ways)

Excel may treat certain entries, particularly numbers that start with zeros, as formulas or numeric data. By adding a leading apostrophe to a cell entry, Excel treats the content as text, preventing it from auto-formatting or interpreting the data. Leading apostrophes can prevent Excel from converting scientific notations and interpreting data as formulas.

In this Excel tutorial, you’ll go through a few simple workarounds that allow you to add leading apostrophes properly in Excel. Like concatenating strings with ampersand (&), using simple functions like CHAR and CONCAT. And for more automatic procedures, you have custom formatting and VBA.

Here are 4 ways to add a leading apostrophe in Excel:

Using Double Quotes (“”) and Ampersand (&)

Using an ampersand (&) is the simplest way to create a formula to concatenate two strings and get the output in text format. On the other hand, any data put between double quotes (“”) will be considered a text string in Excel.

To add a leading apostrophe using double quotes (“”) and ampersand (&) in Excel, we must:

1. Select the target cell.
2. Enter the formula: ="'"&C6
   Where C6 contains the data, you’ll add a leading apostrophe denoted by the string “‘“.
   
3. Press Enter to apply.
   

Here’s how the result column looks after applying the double quotes and ampersand to add a leading apostrophe in Excel.

Applying Excel Text Functions

Excel’s text functions can be used to perform most text operations that you may need.

Here are 2 functions that you can use to add a leading apostrophe in Excel:

Use CHAR Function and Ampersand (&)

The CHAR function is used to retrieve text symbols, including apostrophes, whose code is 39.

Here’s how you can use the CHAR function to add a leading apostrophe in Excel:

1. Select the target cell.
2. Enter the formula:=CHAR(39)&C6
   Where C6 contains the data, you’ll add a leading apostrophe denoted by the formula CHAR(39).
   
3. Press Enter to apply.
   

Here’s how the result column looks after applying the CHAR formula to add a leading apostrophe in Excel.

Use CONCAT Function

Excel’s CONCAT function exists to concatenate two string data in a cell.

Here’s how we can use the CONCAT function to add a leading apostrophe in Excel:

1. Select the target cell.
2. Enter the formula:=CONCAT("'",C6)
   Where C6 contains the data on which you’ll add a leading apostrophe denoted by “‘”.
   
3. Press Enter to apply.
   

Here’s how the result column looks after applying the CONCAT formula to add a leading apostrophe in Excel.

Alternatively, you can also use the CONCATENATE function to add a leading apostrophe in Excel:=CONCATENATE("'",C6)

Applying Format Cells Feature

Excel’s Format Cells feature is used to predefine a data format when inputting data into cells.

Here is how we can use the Format Cells feature to keep the leading apostrophe in Excel:

1. Select a range of cells.
2. Use the keyboard shortcut CTRL+1 to open the Format Cells dialog box.
3. Take these actions in the Format Cells dialog box:
   • Go to the Number tab > Custom.
   • Input the format code in the Type field: ‘@
     The @ represents any data after the apostrophe.
   • Press OK to apply.

   

Now a leading apostrophe will be automatically added when you type in the formatted Excel cells.

Using Excel’s Visual Basic for Applications (VBA)

Excel’s Visual Basic for Applications (VBA) can provide you with a quick and automatic way to get many Excel actions done.

Here are 2 ways to use Excel VBA to add a leading apostrophe in Excel:

Use the Immediate Window Tool in VBA

Here is how you can use VBA’s Immediate Window tool to add a leading apostrophe in Excel:

1. Press ALT+F11 to open the VBA window.
2. Then press CTRL+G to open the Immediate Window tool.
3. Enter the code in the Immediate Window:

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

   C6:C11 is the application range of the worksheet to add a leading zero.
   

4. Save and Run to apply.
   

Note: The apostrophe is hidden, and the Error Warning sign is shown because the data in each cell is now converted into text from a number. Excel treats this as an error. However, you can see the hidden apostrophe in the formula bar.

Tip: To remove the Error Warning sign, you can:

1. Click the Error Warning sign > Ignore Error.
   
2. Repeat this on all the affected cells to remove the Error Warning.
   

Use VBA Sub-Procedure

Here is how we can use a VBA sub-procedure to add a leading apostrophe in Excel:

1. Press ALT+F11 to open the VBA window.
2. From the Project panel > right-click on the target sheet name > Insert > Module.
   
3. Apply this code in Module:

   Sub AddAnApostrophe()
   
   For Each cell In Selection
       cell.Value = "'" & cell.Value
   Next cell
   
   End Sub

   The code will apply a leading apostrophe to the selected cells.

4. Save and Close the window.
5. Go to the View tab > Macros to open the Macros dialog box.
   
6. Select appropriate Macro > Run.
   

As a result, all of your selected cells will have a leading apostrophe in Excel.

Tip: To remove the Error Warning sign, you can: Click the Error Warning sign > Ignore Error.

Download Practice Workbook

Conclusion

In conclusion, adding a leading apostrophe in Excel is a strategic choice to control how Excel handles specific types of data. It helps maintain data integrity, prevents formatting issues, and ensures that your data is accurately represented according to your requirements. To do so, you can use simple formulas using functions like CHAR and CONCAT, or even automate the task by applying VBA.

Feel free to leave any queries you may have in the comments section below.

Frequently Asked Questions

Can I use the leading apostrophe in Excel formulas?

No, the leading apostrophe is used to indicate text and is not part of the actual content.

Does the method for adding a leading apostrophe differ for different alphanumeric data?

No, the process remains the same. Whether dealing with numeric or alphanumeric data, simply enter the apostrophe before the content to ensure it’s treated as text.

Will adding a leading apostrophe affect data sorting in Excel?

No, adding a leading apostrophe does not impact data sorting. Excel considers the actual numeric or alphanumeric value for sorting purposes.

<< Go Back to Excel Cell Format | Learn Excel