Combine Text in Multiple Rows into a single cell using TEXTJOIN Function

Often times you may have the need to perform unusual tasks in Excel. One of such tasks is combining text in multiple rows into a single cell.

Here is a snapshot of a question asked from a friend!

At the first glance it looks like a simple Concatenate function but it is not! Because you’ll have to refer each cell separately in the concatenate function which is as good as doing manual work. Psss..

Here is an interesting way to get this done!

I wrote about a similar problem a while back and solved it using the Justify Tool in Excel and it works wonders if you are working with Excel 2013 or below but if you are using Excel 2016 you are now ready to graduate to TEXTJOIN function

Here is how it works..

Take a look at this data..

1. I am just going ahead with any random data but I am sure you’ll have a more meaningful one!
2. Notice that there are empty cells in between
3. And we want all of text in multiple cells clubbed in a single cell

The TEXTJOIN Function

which is exclusively available in Excel 2016

1. Delimiter – Is nothing but how would you like to separate the text in different cells? You can choose to put a “,” (comma) or anything else, if you leave that empty it takes nothing as a delimiter
2. Ignore Empty –
   • TRUE means – Ignore the empty cells
   • FALSE means – Take them into consideration. In this case what ever delimiter you have chosen, will appear where ever the function finds the empty cells
3. Text – You can select individual cells or a range of cells together and that is the real beauty

DOWNLOAD THE SPREADSHEET FROM BELOW

I have also included a case where you can make your TEXTJOIN result dynamic. Thank you Havovi for asking this 😀

More Quick Tricks for You!

1. Filter Data by Cell Selection (Must Read)
2. Navigate between sheets quickly with the New Window Tool
3. Draw a Chart with 1 keystroke
4. Work on Multiple Sheets at once
5. Select small data points in a Chart