Method 1 – Use the “&” Operator to Concatenate with Delimiters
Let’s use a dataset that has first and last names in two columns. We will join these two columns.
- Copy the following formula in D5 and press Enter.
=B5&" "&C5This equation will join the B5 and C5 cells. We put a space “ ” as a delimiter.
So, we get concatenated text in D5.
- Hold and Drag the cell D5 downward.
- This applies the formula to all the values.
Read More: How to Concatenate with Space in Excel
Method 2 – Using the CONCATENATE Function
- Copy the following formula into D5 and press Enter.
=CONCATENATE(B5," ",C5)This formula joins B5 and C5 together. And for practical purposes, we have put a space “ ” between them.
- Hold and Drag the D5 cell downward.
- You’ll get the full results.
Read More: How to Concatenate Apostrophe in Excel
Method 3 – Concatenate by Adding Parentheses
- Copy the following formula in C5 and press Enter.
=CONCATENATE("(",B5,")")In this formula, there are three strings:
Text1:- “(“
Text2:- B5
Text3:- “)”
- Hold and Drag cell C5 downward.
- This adds parentheses for all the cells.
Read More: How to Bold Text in Concatenate Formula in Excel
Method 4 – Use TEXTJOIN to Concatenate with a Delimiter
Let’s use a dataset of guests. We want all these texts to be joined with a few different delimiters.
- Copy the following formula in the first cell of the Joined Text column and press Enter.
=TEXTJOIN(", ",,B5:B10)In this equation,
“, ” :- Delimiter
,, :- ignore empty cell
B5:B10 :-text range.
- We have joined all the guest names with a comma(,).
- Copy the following equation in the second cell of the result column and press Enter.
=TEXTJOIN("-",,B5:B10)In this equation,
“-” :- Delimiter
,, :- ignore empty cell
B5:B10:-text range.
- This joins all the guest names with “-” as a delimiter.
- Copy the following equation in the last cell of the Joined Text column and press Enter.
=TEXTJOIN(";",,B5:B10)In this equation,
“;” :- Delimiter
,, :- ignore empty cell
B5:B10:- text range.
- This adds guest names with the delimiter “;”.
Read More: How to Concatenate Cells but Keep Text Formatting in Excel
Method 5 – Using the Excel CONCAT Function
We have taken the same dataset as previously shown.
- Copy the following formula in the Joined Text column and press Enter.
=CONCAT(B5," ",B6," ",B7," ",B8," ",B9," ",B10 )
This CONCAT function joins all the text inscribed in the range.
- We have all the guest names joined together with a space between.
Practice Section
You can download this workbook and practice these methods yourself.
Download Practice Workbook
Related Articles
- CONCATENATE vs CONCAT in Excel
- Excel CONCATENATE Showing Formula Not Result
- Concatenate Not Working in Excel
- Opposite of Concatenate in Excel
