Sourav Kundu
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.
Download Practice Workbook
You can download and practice this workbook.
5 Ways to Concatenate with Delimiter in Excel
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 Names in Excel (5 Easy Ways)
2. Using 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 with Space in Excel (3 Suitable Ways)
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 got added parenthesis for all the cells.
Read More: How to Add Parentheses with CONCATENATE Function in Excel
Similar Readings
- How to Concatenate Multiple Cells With Space in Excel (7 Methods)
- Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)
- How to Concatenate Two Columns in Excel with Hyphen (9 Quick Ways)
- Concatenate Columns in Excel (8 Simple Methods)
- How to Concatenate Multiple Cells in Excel (7 Easy Ways)
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 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 Multiple Cells with Comma in Excel (4 Ways)
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.
Read More: How to Combine Rows into One Cell in Excel
Practice Section
You can download this workbook and practice these methods yourself.
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
- Copy CONCATENATE Formula in Excel (5 Easy Ways)
- How to Concatenate Formula and Text in Excel (4 Examples)
- CONCATENATE vs CONCAT in Excel (2 Ideal Examples)
- How to Concatenate and Keep Number Format in Excel
- Concatenate Rows in Excel with Comma (4 Easy Methods)
- How to Concatenate and Keep Currency Format in Excel (3 Methods)
- Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)
