How to Concatenate in Excel (3 Suitable Ways)

Quick View of How to Concatenate in Excel

One of the most important features of Excel is to collect and contain data from various places into a single cell. In other words, to concatenate. Today I will be showing how you can concatenate the values from two or more cells in Excel.


How to Concatenate in Excel (Quick View)

Quick View of How to Concatenate in Excel


Download Practice Workbook


How to Concatenate in Excel

Here we’ve got a data set with the Employee IDs, Employee Names, and Salaries of some employees of a company named Sunflower Group.

Data Set to Concatenate in Excel

Our objective today is to concatenate all the information into a single cell called Merged Information.


Method 1: Using Ampersand (&) Symbol

We can use the Ampersand (&) symbol to concatenate all the information into a single cell.

The formula to concatenate all the information separated by commas(,) will be:

=B4&", "&C4&", "&D4

Concatenate Using Ampersand Symbol in Excel

Now you can drag the Fill Handle to fill the formula to the rest of the cells.

Dragging the Fill Handle in Excel.


Method 2: Using the CONCATENATE Function

You can perform a similar task using the CONCATENATE function in Excel.

Insert all the values that you want to concatenate as the arguments of the function.

Therefore, to concatenate the values in cells B4, C4, and D4 separated by commas(,) will be:

=CONCATENATE(B4,", ",C4,", ",D4)

Concatenate Using the CONCATENATE Function of Excel

Then drag the Fill Handle to fill the formula to the rest of the cells.

Dragging the Fill Handle in Excel.


Method 3: Using the TEXTJOIN Function

When you have a large range of cells to join, using the Ampersand (&) symbol or the CONCATENATE function may be a bit troublesome.

In these cases, you can use the TEXTJOIN function of Excel.

Note: The TEXTJOIN function is only available in Office 365.

The Syntax of the TEXTJOIN function is:

=TEXTJOIN(delimiter,ignore_empty,text1,text2,...)

The TEXTJOIN formula to join the range B4:D4 separated by commas (,) will be:

=TEXTJOIN(", ",TRUE,B4:D4)

Concatente Using the TEXTJOIN Function nof Excel

Then drag the Fill Handle to copy the formula to the rest of the cells.

Dragging the Fill Handle in Excel.

Explanation of the Formula

  • The first argument of the TEXTJOIN function is the delimiter (By which the texts are separated). We inserted a comma (,) in this case..

If you want, you can use anything else as the delimiter. It can be any string.

  • The second argument indicates whether we want to ignore the empty cells within the given range or not.

We set it as TRUE. That means, if there are one or more empty cells within our given range, we want to ignore those.

  • The third argument is the range that we want to concatenate. It is B4:D4 for the first employee.

Conclusion

Using these methods, we can concatenate the values from two or more cells into one single cell in Excel. Do you have any questions? Feel free to ask us.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo