Excel concatenation is a useful and frequently used feature. If you want to join or delimit text, this article might be useful to you. In this article, we will show you some simple methods of Excel Concatenate, especially with the delimiter.
How to Concatenate with Delimiter in Excel: 5 Ways
There are many ways to Concatenate in Excel. We will use “&” Operator along with CONCATENATE, TEXTJOIN, and CONCAT Functions for Excel concatenate. In these methods, we will also delimit the texts.
1. Use of “&” Operator to Concatenate with Delimiter
Let’s have a dataset that has first and last names in two columns. We will join these two columns with the “&” operator.
➤ Write the following formula in D5 and press ENTER.
=B5&" "&C5This equation will join the B5 and C5 cells. And we have put a space “ ” as a delimiter.
So, we get concatenated text in D5.
➤ Now, Hold and Drag the D5 downward.
Thus, we have concatenated all the values.
Read More: How to Concatenate with Space in Excel
2. Using the CONCATENATE Function
We can use the CONCATENATE function to complete our tasks. We have the same dataset for this method.
➤ Write the following formula in 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.
So, we have the first concatenated text.
➤ Now, Hold and Drag the D5 cell downward.
Thus we have concatenated all the values with space as its delimiter.
Read More: How to Concatenate Apostrophe in Excel
3. Concatenate by Adding Parentheses
In this section, we will learn how to add parenthesis.
We have taken a dataset of different characters.
➤ Write the following formula in C5 and press ENTER.
=CONCATENATE("(",B5,")")In this formula, there are three texts.
Text1:- “(“
Text2:- B5
Text3:- “)”
So, we get parenthesis for the first value.
➤ Now, Hold and Drag the C5 downward.
Thus we have added parenthesis for all the cells.
Read More: How to Bold Text in Concatenate Formula in Excel
4. Use of TEXTJOIN to Concatenate with Delimiter
Now, We will join texts with different delimiters. For this, we will use the TEXTJOIN function.
For that, we have taken a dataset of guests. We want all these texts to be joined with some delimiter.
➤ Write the following formula in the first cell of the Join Text column and press ENTER.
=TEXTJOIN(", ",,B5:B10)In this equation,
“, ” :- Delimiter
,, :- ignore empty cell
B5:B10 :-text range.
So, we have joined all the guest names with a comma(,).
Let’s use a different delimiter.
➤ Write the following equation in the second cell of the Text Join column and press ENTER.
=TEXTJOIN("-",,B5:B10)In this equation,
“-” :- Delimiter
,, :- ignore empty cell
B5:B10:-text range.
Thus, we have joined all the guest names with “-” as a delimiter.
➤ Now, write 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.
So, we have all the guest names joined together with the delimiter “;”.
Read More: How to Concatenate Cells but Keep Text Formatting in Excel
5. Using Excel CONCAT Function
Now, we will use the CONCAT function to join all the text.
We have taken the same dataset as previously shown.
➤ Write 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.
So, we have all the guest names joined together without any delimiter.
Practice Section
You can download this workbook and practice these methods yourself.
Download Practice Workbook
You can download and practice this workbook.
Conclusion
So, we learned how in Excel, we can concatenate with the delimiter. Hope our effort has been useful to you. If you have any questions or suggestions, you can let us know through the comment section.
Related Articles
- CONCATENATE vs CONCAT in Excel
- Excel CONCATENATE Showing Formula Not Result
- Concatenate Not Working in Excel
- Opposite of Concatenate in Excel
