Add Single Quotes to Excel Cells Using VBA

In our previous post we learnt the uses of adding single quotes to the beginning of cells.

Uses of Adding Single Quotes to Start of Excel Cells

In this lesson you will learn how to add single quotes to Excel cells using VBA. First of all, let’s see how we can do this manually in Excel. Because Excel shows unusual behavior when you add a single quote at the beginning of a cell.

Assume you want to add ‘53 to an Excel cell. Then when you enter '53 to an Excel cell it will show only 53 with a small green triangle at the upper left corner of the cell.

But in the formula bar you can still see the single quote before the number.

Also when you select the cell a small icon will appear and if you take your cursor on top of it, you will see this kind of message.

Then what if we want to see a single quote at the beginning of the cell. Solution is simple. You need to add two single quotes.

Here one single quote is shown in the cell. However you can see both single quotes in the formula bar.

Assume you want to add single quotes at both left and right of the content of the cell.
Example - '53'
How can we do that? To do this you need to add two single quotes at the beginning and only one single quote at the end like this.
''53'

Now let’s see how we can add single quotes to Excel cells using VBA. When we automate this in VBA we need to consider the above behavior too.
Let’s consider this sample Excel sheet.

Sheet has 10 values in column A. Assume we want to add a single quote in front of each value. There are two ways to do this in VBA. We can use either ASCII code or we can use single quotes inside double quotes. First let’s see how we can do this using ASCII code. Assume the name of the worksheet is “My Data”.

Method 1 - Using ASCII code

Sub Add_Single_Quote()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("My Data")

   For i = 1 To 10
     WS.Range("A" & i).Value = Chr(39) & WS.Range("A" & i).Value
   Next i

End Sub

In the above code Chr(39) represents the single quote character. Here you can find the full list of ASCII codes.

ASCII Table

Also a For loop is used to iterate through each value in column A.

Method 2 - Using Single quotes directly inside double quotes

Sub Add_Single_Quote_Method2()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("My Data")

   For i = 1 To 10
     WS.Range("A" & i).Value = "'" & WS.Range("A" & i).Value
   Next i

End Sub

Below is the result we get when we run any of the above subroutines.

Like in the manual scenario, we only get a green triangle at the upper left corner instead of the single quote. To show a single quote we need to add two single quotes using VBA. For that we can modify the above two macro as follows.

Method 1

Sub Add_Single_Quote()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("My Data")

   For i = 1 To 10
     WS.Range("A" & i).Value = Chr(39) & Chr(39) & WS.Range("A" & i).Value
   Next i

End Sub

Method 2

Sub Add_Single_Quote_Method2()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("My Data")

   For i = 1 To 10
     WS.Range("A" & i).Value = "''" & WS.Range("A" & i).Value
   Next i

End Sub

Now we will see a single quote before each value as expected.

Next let’s see how we can add a single quote at both sides of the cell content. Let’s consider this new sheet. Assume the name of the sheet is “Fruits”.

Below are the two methods to add single quotes at either side of the fruit names.

Method 1

Sub Add_Single_Quotes_At_Both_Sides()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("Fruits")

   For i = 1 To 8
     WS.Range("A" & i).Value = Chr(39) & Chr(39) & WS.Range("A" & i).Value & Chr(39)
   Next i

End Sub

Method 2

Sub Add_Single_Quotes_At_Both_Sides_Method2()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("Fruits")

   For i = 1 To 8
     WS.Range("A" & i).Value = "''" & WS.Range("A" & i).Value & "'"
   Next i

End Sub

You will get this result if you run any of the above macros.