A decade ago, I wrote a blog post about the same in an no-formula approach. Ten years has past and Excel has evolved so much. With Excel 365, the same task can be performed in an efficient and effective manner by using TEXTJOIN function. See screenshot below:

Isn’t it nice?

The syntax is simple:

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

where
delimiter is the separator(s) you want, it could be number, space, symbol, or text (enclosed by double quotation marks””);
ignore_empty is the argument for including empty cells or not. If TRUE, Excel ignores empty cells;
text1 is the text you want to join. Usually, it’s a range of cell.
[text2] up to [text252] are optional, meaning we can apply this function to different ranges (not necessarily a continuous range).

When ignore empty is FALSE

It’s straight-forward!

When the ranges are not continuous

No problem at all. Even the ranges are mixed with vertical and horizontal ranges.

When the range is 2D

TEXTJOIN works fine too. We just need to pay attention to the direction of TEXTJOIN.

What if you want to scan texts by column instead?

A simple twist to the formula would do!

TEXTJOIN with different delimiters

Although I cannot think of a practical use case right now, it is doable with ease!

As you can see, using TEXTJOIN to combine contents from different cells is much more efficient than using the manual trick I shared a decade ago. If you are using Excel 365, don’t miss all these “new” functions. These functions bring your productivity to the next level.

You may download the sample workbook to follow along.