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.

how to add a leading apostrophe in Excel

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 “‘“.
    formula with double quotes and ampersand
  3. Press Enter to apply.
    add leading apostrophe with double quotes and ampersand

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

add leading apostrophe with double quotes and ampersand 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).
    char formula for apostrophe
  3. Press Enter to apply.
    adding leading apostrophe with char function

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

add leading apostrophe with char function 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 “‘”.
    concat function to add leading apostrophe
  3. Press Enter to apply.
    add leading apostrophe woth concat function

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

add leading apostrophe with concat function in Excel

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

using CONCATENATE function


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.

    configuring format cells dialog box

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

add leading apostrophe in Excel using Format 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.
    inputting code in the immediate window

  4. Save and Run to apply.
    add leading apostrophe using Immediate Window tool

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.
    Ignore error option in Error Warning
  2. Repeat this on all the affected cells to remove the Error Warning.
    remove error warning sign

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.
    opening vba 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.
    opening macro
  6. Select appropriate Macro > Run.
    selecting macro from the macro window

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

add leading apostrophe with VBA macro

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

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

8 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

  3. How do you add an apostrophe that doesn’t actually show as an apostrophe?

    For example, if you type “‘3” into a cell, the value in the cell will appear as simply “3”; if you enter the cell with the cursor, you will see the apostrophe, but it is otherwise hidden.

    But when you use the first several methods shown here, you get a visible apostrophe. Is is possible to use a formula but have the apostrophe hidden?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto May 16, 2024 at 10:12 AM

      Hello Watt

      Thanks for visiting our blog and sharing your queries. I have reviewed your requirements. Unfortunately, you cannot directly use any formulas to add a hidden apostrophe. Formulas can only output visible characters; not even a User-defined function can do that. So, you can type the apostrophe before the number or text to do so, though it is very exhausting when it comes to lots of cells.

      Don’t worry! I have developed a sub-procedure that will add a hidden apostrophe to cells in a selected range without displaying it with one click.

      SOLUTION Overview:

      Adding a hidden apostrophe to cells in a selected range without displaying it

      Excel VBA Sub-procedure:

      Sub AddHiddenApostrophe()
      
          Dim cell As Range
          For Each cell In Selection
              If Not IsEmpty(cell) Then
                  cell.value = "'" & cell.value
              End If
          Next cell
      
      End Sub

      Hopefully, you will find the solution helpful. I have attached the solution workbook as well; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  4. Hi Lutfor thank you this is exactly what I needed.

    • Hello Tara Man,

      You are most welcome. We are glad to hear that you got what you needed. Keep learning Excel with us.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo