TextJoin function in Excel

Where we have simple requirements, Excel can be very useful for analysing, grouping and clustering survey data. However, to get the most out of the simple analysis which Excel does, being aware of some of the on board tools can certainly make a straight-forward job even more powerful.

For example, there are several ways to concatenate elements within a range. We can use a formula such as concatenate, or we can use the more streamlined version, TextJoin.

TextJoin allows us to collapse materials and show a spread of answers in one cell. It allows the formula to ignore any blanks in the formula and only concatenate those items which contain more than zero. This can be particularly useful if we are compiling a range of answers into one field. The image accompanying this post shows how all the fields are pulled into one, including specifying a delimiter: in this case, a space, and a comma.

A deliminator could be a hyphen, a line of text, or simply a space.

The structure of putting together a TextJoin function is as follows, with the formula first, and the example following:

=TEXTJOIN(delimiter, TRUE, Cell_range)

=TEXTJOIN(", ",TRUE,B3:B8)

This is a very useful formula.

Sam

References:

CFI. (2019). TEXTJOIN Function. https://corporatefinanceinstitute.com/resources/excel/functions/textjoin-function/

ExtendOffice. (2014). Concatenate Cells But Ignore Blanks. https://corporatefinanceinstitute.com/resources/excel/functions/textjoin-function/