How to Concatenate Apostrophe in Excel (6 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel we have to differentiate between entries using various delimiters, then concatenating the delimiter would be a handful there. In this article, we use Ampersand Operator, functions like CONCAT, CONCATENATE, CHAR, TEXT, TEXTJOIN, and VBA Macro code to concatenate apostrophes in Excel. Here, we will use an apostrophe as a delimiter to differentiate between First Name and Last Name. In this article, we will discuss six effective methods to concatenate apostrophes in Excel. Now, let’s start this article and explore these methods.

The following image demonstrates the overview of the methods that we will discuss in this article.

Overview of the methods to Concatenate Apostrophe in Excel


How to Concatenate Apostrophe in Excel: 6 Easy Ways

In this section of the article, we will learn six simple, yet effective methods to concatenate apostrophes in Excel. Let’s say, we have a List of Employee Name containing First Name and Last Name. Our goal is to get the Full Names appearing with apostrophes. The apostrophes are to indicate First Name and Last Name separately in Full Names.

Sample Dataset

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.


1. Using Ampersand Operator (&) to Concatenate Apostrophe

The Ampersand operator (&) works as a connector to concatenate texts. We can use the Ampersand operator (&) to concatenate First Name and Last Name to bring out the Full Name with an apostrophe as a separator. Now, let’s follow the steps mentioned below to do this.

Steps:

  • Firstly, type the following formula in cell D5.
="'"&B5&"'"&" '"&C5&"'"

In the formula, the Quotations sit as single apostrophes on both sides of each cell reference (i.e., B5 and C5) while concatenating them as Full Name.

Using Ampersand Operator to Concatenate Apostrophe in Excel

  • After that, press ENTER then Drag the Fill Handle to come up with Full Names with apostrophes on both sides of First Name and Last Name.

Outputs obtained after using Ampersand operator

You can change the orientation of the Full Names may look like apostrophes. You can put single or double Apostrophes between First Name and Last Names to declare them as different entities in Full Names.

Read More: How to Concatenate with Delimiter in Excel


2. Utilizing CONCAT Function to Concatenate Apostrophe

If you are using Microsoft Excel 2019 or onwards versions, you can use the CONCAT function to concatenate apostrophes in texts. Otherwise move to the next method (i.e., CONCATENATE Function) to do the job. Now, let’s use the steps discussed in the following section to concatenate apostrophes in Excel using the CONCAT function.

Steps:

  • Firstly, insert the following formula in D5.
=CONCAT("'",B5,"'"," '",C5,"'")

Here in the formula, we use apostrophes () as Delimiters to differentiate between First Name and Last Name.

Using CONCAT function to Concatenate Apostrophe in Excel

  • Following that, hit ENTER after that Drag the Fill Handle.

As a result, all the First Name and Last Name will appear with quoted Apostrophes constituting Full Names as shown in the following picture.

Applying Fill Handle to get the Full Name for remaining employees


3. Applying the CONCATENATE Function to Concatenate Apostrophe

In case you using Microsoft Excel’s versions before 2019, you can use the CONCATENATE function instead of the CONCAT function that we discussed in the second Method. The CONCATENATE function has similar syntax and arguments to the CONCAT function. Now, let’s follow the instructions outlined below to use the CONCATENATE function in Excel.

Steps:

  • Firstly, write the following formula in D5.
=CONCATENATE("'",B5,"'"," '",C5,"'")

Applying CONCATENATE function to Concatenate Apostrophe in Excel

  • Afterward, press ENTER then drag the Fill Handle.

Subsequently, the Full Names will be concatenated with apostrophes as demonstrated in the image below.

Final output after using CONACATENATE function and Fill Handle

Read More: How to Concatenate with Space in Excel


4. Implementing CHAR and TEXT Functions with Ampersand Operator

In this section of the article, we will use a combination of the CHAR, and TEXT functions with the Ampersand operator to concatenate apostrophes in Excel. So, let’s follow the instructions mentioned below to do this.

Steps:

  • Firstly, use the following formula in cell D6.
=CHAR(39)&TEXT(B6,"")&CHAR(39)&" "&CHAR(39)&TEXT(C6,"")&CHAR(39)

Here, cells B6 and C6 refer to the first cells of the First Name and the Last Name columns respectively.

Formula Breakdown

  • Firstly, the CHAR function will return the character specified by the code number inside it.
    • Here, 39 → It refers to the number argument of the CHAR function.
    • Output “‘”.
  • Now, the formula becomes →  “‘”&TEXT(B6,””)&”‘”&” “&”‘”&TEXT(C6,””)&”‘”.
  • Following that, in the first TEXT function,
    • B6 → This indicates the value argument.
    • “” → It is the format_text argument.
    • Output “Jane”.
  • Similarly, from the second TEXT function,
    • Output “Doe”.
  • Finally, the formula becomes → “‘”&”Jane”&”‘”&” “&”‘”&”Doe”&”‘”.
  • Output ‘Jane’ ‘Doe’.
  • Following that hit ENTER from your keyboard and use Excel’s AutoFill feature.

Using CHAR, and TEXT Functions with Ampersand Operator to Concatenate Apostrophe in Excel

Subsequently, you will have the Full Names concatenated with the Ampersand operator as shown in the image below.

Using Fill Handle to get the rest of the outputs

Read More: How to Bold Text in Concatenate Formula in Excel


5. Using TEXTJOIN Function

Using the TEXTJOIN function is another smart way to concatenate apostrophes in Excel. Here, we will use a combination of the TEXTJOIN function and the Ampersand operator. So, let’s use the procedure discussed in the following section to do this.

Steps:

  • Firstly, type the following formula in D5.
="'"&TEXTJOIN("' '",TRUE,B5,C5)&"'"

Here, “‘ ‘” double apostrophes are the delimiter argument, TRUE is the ignore_empty argument, and B5, C5 are the text references.

Utilizing TEXTJOIN function to Concatenate Apostrophe in Excel

  • After that, press ENTER then Drag the Fill Handle to bring up the Full Names with apostrophes in between names.

Outputs got after using the TEXTJOIN function and Fill Handle command in Excel

Note: You can place single or multiple Apostrophes between names as delimiters.

Read More: How to Concatenate Cells but Keep Text Formatting in Excel


6. Incorporating VBA Macro Code to Concatenate Apostrophe

In this action of the article, we will incorporate VBA Macro code to concatenate apostrophes in Excel. Now, let’s follow the steps outlined below to do this.

Steps:

  • Firstly, go to the Developer tab from Ribbon.
  • After that, choose the Visual Basic option from the Code group.

Using Developer option to open VBA editor window

As a result, the Microsoft Visual Basic for Applications window will appear on your worksheet.

Microsoft Visual Basic for Applications window

  • Now, go to the Insert tab in the Microsoft Visual Basic for Applications window.
  • Subsequently, choose the Module option from the drop-down.

Inserting new Module to write VBA code

  • After that, write the following code in the newly created Module.
Sub concatenate_apostrophe()
Dim a As Integer, b As Integer, starting_row As Integer
starting_row = 5
b = 2
For a = starting_row To Selection.Rows.Count + (starting_row - 1)
Cells(a, b + 2).Value = "''" & Cells(a, b).Value & "'" & _
" '" & Cells(a, b + 1).Value & "'"
Next a
End Sub

Writing VBA code

Code Breakdown

  • Firstly, we created a sub-procedure named concatenate_apostrophe.
  • After that, we introduced three variables a, b, and starting_row as integers.
  • Then, we defined the values of the starting_row variable as 5.
  • Afterward, we initiated a For Next loop to concatenate apostrophes.
  • Next, we closed the For Next loop.
  • Lastly, we ended the sub-procedure.
  • After writing the code, click on the Save option.

Saving written VBA code

  • Now, use the keyboard shortcut ALT + F11 to return to the worksheet.
  • Afterward, select the dataset and use the keyboard shortcut ALT + F8 to open the Macro dialogue box.
  • Following that, choose the concatenate_apostrophe option in the Macro dialogue box.
  • Finally, click on Run.

Running Macro to Concatenate Apostrophe in Excel

Consequently, you will have the following outputs as demonstrated in the following image.

Outputs got after using the VBA Macro feature in Excel


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Sample Practice Section provided in each worksheet of the Practice Workbook.


Download Practice Workbook


Conclusion

In the article, we use Ampersand Operator (&) and multiple functions such as CONCAT, CONCATENATE, CHAR, TEXT, and TEXTJOIN as well as VBA Macro Code to concatenate apostrophes in Excel. Ampersand Operator (&) joins entries as well as delimiters. The CONCAT and CONCATENATE functions concatenate cell references along with delimiters like Apostrophes. The TEXT function does this job by combining with Ampersands. The TEXTJOIN function takes Apostrophes as delimiters and concatenates Names. I Hope the above-mentioned methods do the jobs you seek. Comment, if you need further queries and have something to add.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo