TEXTJOIN Function
What is the =TEXTJOIN function?
The ability to concatenate cells (i.e. join them together in a series) proves very useful when working with Excel as it allows users to create dynamic headers and footers. One of the main benefits of this is that users can simply input a few bits of data into separate cells and Excel can combine the content of these cells, along with other text strings, into a longer string.
For example, one of the most common combinations would combine units and currency in a header which might read “All figures in millions of USD”. To make this truly dynamic we would want to be able to replace both the units and the currency with alternatives such as “thousands” or “GBP” respectively and have the header update automatically to “All figures in thousands of GBP”. The other text would remain the same.
Traditionally the two main ways to do this involved using either the “=CONCATENATE” function or the “&” symbol, both of which require all the cells to be selected individually (as opposed to selecting a range of cells), and the spaces (or other delimeters) need to be included as well. This makes the formulas long and cumbersome as each individual space/delimeter needs to be added separately.
Key Learning Points
- The =TEXTJOIN function is an in built function in Excel which allows strings of text data to be concatenated into longer text strings
- As with many processes in Excel, there are multiples to perform the same thing
- The =CONCATENATE function or the “&” symbol both allow text strings to be concatenated but the cells must be selected individually
Example
E.g. let us imagine a formula that joins together the contents of the cell range A1 to A4.
We could either use:
=CONCATENATE(A1,” “,A2,” “,A3,” “,A4)
Or
= A1&” “&A2&” “&A3&” “&A4
With the =TEXTJOIN function this can be achieved much more elegantly with:
=TEXTJOIN(“ “,1,A1:A4)
The first term in the formula specifies a common delimeter, which in this case is a space: “ “
The second term allows you to choose whether blank cells should be included or ignored: 1 = Ignore. This means that extra spaces will not be added if empty cells are contained in the range
The third and subsequent terms specify the cells that you want to join together. Rather than select them individually though, you can actually select the range which makes the formula much smaller and easier to audit.
Download the accompanying Excel file to look at some examples.