Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Concatenate with Delimiter in Excel (5 Easy Ways)

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.

How to Concatenate with Delimiter in Excel

➤ Write the following formula in D5 and press ENTER.

=B5&" "&C5

This equation will join the B5 and C5 cells. And we have put a space “ ” as a delimiter.

Concatenate with Delimiter in Excel

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.

Concatenate with Delimiter in Excel

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:- “)”

Concatenate with Delimiter in Excel

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.

Concatenate with Delimiter in Excel

Read More: How to Add Parentheses with CONCATENATE Function in Excel


Similar Readings


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.

Concatenate with Delimiter in Excel

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.

Concatenate with Delimiter in Excel

➤ 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.

Concatenate with Delimiter in Excel

➤ 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

Sourav Kundu

Sourav Kundu

Bio: Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo