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, functions like CONCAT, CONCATENATE, and TEXT, TEXTJOIN as well as VBA Macro Code to concatenate apostrophe in Excel. In this case, we use an apostrophe as a delimiter to differentiate between First and Last Name.

Let’s say, we have Employee Names containing First and Last Names and we want to get Full Names appearing with apostrophes. The apostrophes are to indicate First and Last Names separately in Full Names.

Dataset-How to Concatenate Apostrophe in Excel

Dataset for Download

6 Easy Ways to Concatenate Apostrophe

Method 1: Using Ampersand (&)

The Ampersand (&) operator works as a connector to concatenate texts. We can use Ampersand (&) to concatenate First and Last Name to bring out the Full Name with an apostrophe as a separator.

Step 1: Type the following formula in any blank cell (i.e.,D4).

="'"&B4&"'"&" '"&C4&"'"

In the formula, The Quotations sit single Apostrophes on both sides of each cell reference (i.e., B4 and C4) while concatenating them as Full Name.

Ampersand-How to Concatenate Apostrophe in Excel

Step 2: Press ENTER then Drag the Fill Handle to come up with Full Names with Apostrophes on both sides of First and Last Names.

Ampersand final result-How to Concatenate Apostrophe in Excel

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

Read More: Concatenate Not Working in Excel (3 Reasons with Solutions)

Method 2: Using 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.

The syntax of the CONCAT function is

CONCAT( text1, [ text2, ... text_n ] )

 We can use any cell references or delimiters as text in the function.

Step 1: Insert the following formula in any cell (i.e., D4).

=CONCAT("'",B4,"'"," '",C4,"'")

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

Concat function

Step 2: Hit ENTER after that Drag the Fill Handle. All the First and Last Names appear with quoted Apostrophes constituting Full Names.

Concat function result-How to Concatenate Apostrophe in Excel

You can insert Apostrophes in positions you prefer using the CONCAT function.

Method 3: Using 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 we discuss in Method 2.

The CONCATENATE function has similar syntax and arguments to the CONCAT function.

Step 1: Write the following formula in any blank cell (i.e., D4)

=CONCATENATE("'",B4,"'"," '",C4,"'")

Concatenate function

Step 2: Press ENTER then Drag the Fill Handle to come up with a similar result to the following image.

Concatenate function result-How to Concatenate Apostrophe in Excel


Similar Readings:


Method 4: Using TEXT Function and Ampersand

The task can be done by using the TEXT function as well. The syntax of the TEXT function is

<b><span style="font-size: 11.0pt; font-family: 'Arial',sans-serif; color: black;">Text(Value, format_text)</span></b>

In format_text we cannot include the Apostrophe delimiter. Thus, we use Ampersands to concatenate Apostrophes in Names.

In this method, we first concatenate both First and Last Names with Apostrophes then join them to constitute Full Names.

Step1: Type the following formula in any blank cell (i.e., C4)

 =CHAR(39)&TEXT(B4,"")&CHAR(39)

Inside the formula, CHAR(39) is the ASCII character code number for Apostrophe.

text function and ampersand-How to Concatenate Apostrophe in Excel

Step 2: Press ENTER and Drag the Fill Handle to insert Apostrophes on both sides of the First Names.

text function and ampersand

Repeat Steps 1 and 2 to do the same thing with Last Names. After repeating Steps 1 and 2, the outcomes will be the same as the image below.

text function and ampersand

Step 3: Paste the below formula in any cell (i.e., F4) to form the Full Names.

=C4&E4

Full Name

Step 4: Hit ENTER and Drag the Fill Handle to form the Full Names with Apostrophes.

text function and ampersand final result-How to Concatenate Apostrophe in Excel

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

Method 5: Using TEXTJOIN Function

Another function we can use is TEXTJOIN. The syntax of the TEXTJOIN function is

TEXTJOIN(delimiter, ignore_empty, text1, [ text2, ... text_n ] )

Inside the formula,

delimiter; is a string that sits between the text references.

ignore_empty; decides whether empty values are considered in the results. Selecting TRUE ignores the empty values and FALSE otherwise.

text1, [text2, … text_n]; are texts to be concatenated.

In this case, we just use two Apostrophes between Names to differentiate.

Step 1: Type the following formula in any cell (i.e., D4)

=TEXTJOIN("' '",TRUE,B4,C4)

 In the formula,

“‘ ‘”; double Apostrophes is the delimiter.

TRUE; is the ignore_empty.

B4,C4; are the text references.

textjoin function-How to Concatenate Apostrophe in Excel

Step 2: Press ENTER then Drag the Fill Handle to bring up the Full Names with Apostrophes in between Names.

textjoin function final result

You can place single or multiple Apostrophes between Names as delimiters.

Method 6: Using VBA Macro Code to Concatenate Apostrophe

We can place Apostrophes on both sides of First and Last Names by running a couple of lines of Macro Code.

Step 1: Select both the First and Last Name columns.

vba macro code -How to Concatenate Apostrophe in Excel

Step 2: Press ALT+F11 altogether. Microsoft Visual Basic window opens up. From the Toolbar, Click on Insert > Select Module.

Module

Step 3: In the Module, Paste the following Macro Code. Press F5 to run the Macro Code.

Sub ConcatenateApostrophe()
For Each cell In Selection
cell.Value = "''" & cell.Value & "'"
Next cell
End Sub

Macro code

The code uses cell.Value formula to format selected cells with Apostrophes.

Step 3: Back to the Worksheet, you’ll see both Names are quoted with single Apostrophes on both sides.

Paste the following formula in any cell (i.e., D4).

=B4&C4

Applying ampersand

 Step 4: Press ENTER then Drag the Fill Handle to form the Full Names with Apostrophes.

vba macro code final result-How to Concatenate Apostrophe in Excel

Read More: How to Concatenate String and Integer using VBA

Conclusion

In the article, we use Ampersand (&) and multiple functions such as CONCAT, CONCATENATE, TEXT, and TEXTJOIN as well as VBA Macro Code to concatenate apostrophes. Ampersand (&) 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 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

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

ExcelDemy
Logo