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 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).
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.
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.
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.
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).
Here in the formula, we use Apostrophes (‘) as Delimiters to differentiate between First and Last Names.
Step 2: Hit ENTER after that Drag the Fill Handle. All the First and Last Names appear with quoted Apostrophes constituting Full Names.
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)
Step 2: Press ENTER then Drag the Fill Handle to come up with a similar result to the following image.
- 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)
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)
Inside the formula, CHAR(39) is the ASCII character code number for Apostrophe.
Step 2: Press ENTER and Drag the Fill Handle to insert Apostrophes on both sides of the First Names.
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.
Step 3: Paste the below formula in any cell (i.e., F4) to form the Full Names.
Step 4: Hit ENTER and Drag the Fill Handle to form the Full Names with Apostrophes.
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)
In the formula,
“‘ ‘”; double Apostrophes is the delimiter.
TRUE; is the ignore_empty.
B4,C4; are the text references.
Step 2: Press ENTER then Drag the Fill Handle to bring up the Full Names with Apostrophes in between Names.
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.
Step 2: Press ALT+F11 altogether. Microsoft Visual Basic window opens up. From the Toolbar, Click on Insert > Select 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
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).
Step 4: Press ENTER then Drag the Fill Handle to form the Full Names with Apostrophes.
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.