How to Insert an Apostrophe in Excel VBA

When working with Excel VBA, you might find yourself needing to insert an apostrophe into a string or directly into a cell. Apostrophes can denote possession or form contractions in text, and they are also used in Excel to ensure that numbers are treated as text.

However, due to their special meaning in VBA as string delimiters, adding them to your code can be tricky. This tutorial will guide you on how to correctly insert an apostrophe using Excel VBA.

Steps to Insert an Apostrophe at the Start of a String

To insert an apostrophe at the beginning of a string to make Excel treat it as text, follow these steps:

  1. Use two double quotation marks to insert a single apostrophe within a string.
  2. Use the VBA Concatenation operator (&) to concatenate an apostrophe with the rest of your string.
  3. Write the VBA code in the form of Range("CellAddress").Value = "''" & YourString, replacing CellAddress with the appropriate cell reference and YourString with the value you want to precede with an apostrophe.

Here is a simple example where we are inserting an apostrophe at the beginning of the text “Text” so that it is treated as a string by Excel:

Steps to Insert an Apostrophe within a String

If you need to include an apostrophe as part of a string inside your Excel worksheet, you should follow a slightly different approach:

  1. Escaping the apostrophe with another apostrophe if you’re entering it directly as part of a string value.
  2. Use the following VBA code structure: Range("CellAddress").Value = "YourString's example", where you need to double the apostrophe like “YourString”s example”.
  3. Assign this string to the Value property of a cell.

In this example, we are trying to write “It’s a great day!” in cell A2:


Full Code


Using Excel VBA to insert an apostrophe either at the beginning of a string or within a string is straightforward once you know that you need to double up the apostrophe to escape it. Remember that when concatenating with other strings or characters, the ampersand (&) operator is your friend. With the right syntax, you can easily manipulate text in Excel to include apostrophes wherever you need them. The examples provided should give you a clear guide on how to accomplish this in your own projects.

Posted in vba