Method 1 – Using Ampersand Operator (&) to Concatenate Apostrophe
Steps:
- Type the following formula in cell D5.
="'"&B5&"'"&" '"&C5&"'"The Quotations sit as single apostrophes on both sides of each cell reference (i.e., B5 and C5) while concatenating them as Full Name.
- Press ENTER then Drag the Fill Handle to come up with Full Names with apostrophes on both sides of First Name and Last Name.
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.
Method 2 – Utilizing CONCAT Function to Concatenate Apostrophe
Steps:
- Insert the following formula in D5.
=CONCAT("'",B5,"'"," '",C5,"'")Use apostrophes (‘) as Delimiters to differentiate between First Name and Last Name.
- Hit ENTER after that Drag the Fill Handle.
All the First Name and Last Name will appear with quoted Apostrophes constituting Full Names as shown in the following picture.
Method 3 – Applying the CONCATENATE Function to Concatenate Apostrophe
Steps:
- Write the following formula in D5.
=CONCATENATE("'",B5,"'"," '",C5,"'")
- Press ENTER then drag the Fill Handle.
The Full Names will be concatenated with apostrophes, as demonstrated in the image below.
Method 4 – Implementing CHAR and TEXT Functions with Ampersand Operator
Steps:
- 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
- The CHAR function will return the character specified by the code number inside it.
- 39 → It refers to the number argument of the CHAR function.
- Output → “‘”.
- The formula becomes → “‘”&TEXT(B6,””)&”‘”&” “&”‘”&TEXT(C6,””)&”‘”.
- In the first TEXT function,
- B6 → This indicates the value argument.
- “” → It is the format_text argument.
- Output → “Jane”.
- From the second TEXT function,
- Output → “Doe”.
- The formula becomes → “‘”&”Jane”&”‘”&” “&”‘”&”Doe”&”‘”.
- Output → ‘Jane’ ‘Doe’.
- Hit ENTER from your keyboard and use Excel’s AutoFill feature.
Have the Full Names concatenated with the Ampersand operator, as shown in the image below.
5. Using TEXTJOIN Function
Steps:
- Type the following formula in D5.
="'"&TEXTJOIN("' '",TRUE,B5,C5)&"'"“‘ ‘” double apostrophes are the delimiter argument, TRUE is the ignore_empty argument, and B5, C5 are the text references.
- 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.
Method 6 – Incorporating VBA Macro Code to Concatenate Apostrophe
Steps:
- Go to the Developer tab from Ribbon.
- Choose the Visual Basic option from the Code group.
The Microsoft Visual Basic for Applications window will appear on your worksheet.
- Go to the Insert tab in the Microsoft Visual Basic for Applications window.
- Choose the Module option from the drop-down.
- 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
Code Breakdown
- Created a sub-procedure named concatenate_apostrophe.
- Introduced three variables a, b, and starting_row as integers.
- Defined the values of the starting_row variable as 5.
- Initiated a For Next loop to concatenate apostrophes.
- Closed the For Next loop.
- Ended the sub-procedure.
- After writing the code, click on the Save option.
- Use the keyboard shortcut ALT + F11 to return to the worksheet.
- 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.
- Click on Run.
You will have the following outputs as demonstrated in the following image.
Download Practice Workbook
