How to Concatenate Apostrophe in Excel (6 Easy Ways)

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 as well as VBA Macro code to concatenate apostrophe 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 apostrophe 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.

Download Practice Workbook

6 Easy Ways to Concatenate Apostrophe in Excel

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.

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.

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.

• 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.

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: Concatenate Not Working in Excel (3 Reasons with Solutions)

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 apostrophe in Excel using the CONCAT function.

Steps:

• Firstly, insert the following formula in D5.

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

• 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.

3. Applying CONCATENATE Function to Concatenate Apostrophe

In case you using Microsoft Excel’s versions prior to 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.

• Afterward, press ENTER then drag the Fill Handle.

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

Similar Readings:

• Combine Multiple Columns into One Column in Excel
• Concatenate Numbers in Excel (4 Quick Formulas)
• How to Combine Date and Text in Excel (5 Ways)
• Concatenate in Excel (3 Suitable Ways)
• How to Concatenate Multiple Cells in Excel (8 Quick Approaches)

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 apostrophe in Excel. So, let’s follow the instructions mentioned below to do this.

Steps:

• Firstly, use the following formula in cell D6.

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

• Following that, hit ENTER from your keyboard and use Excel’s AutoFill feature.

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

Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)

5. Using TEXTJOIN Function

Using the TEXTJOIN function is another smart way to concatenate apostrophe 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.

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

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

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

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.

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

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

• 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

• After writing the code, click on the Save option.

• 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.

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

Practice Section

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

Read More: How to Concatenate String and Integer using VBA

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 apostrophe 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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.

Related Articles

• Concatenate Date and Time in Excel (4 Formulas)
• How to Concatenate with Space in Excel (3 Suitable Ways)
• Concatenate Range in Excel (For Both Old and New Versions)
• VBA to Concatenate Range with Separator in Excel (3 Ways)
• How to Concatenate Two Columns In Excel (5 Methods)