When storing names in Excel, it is usual to create three separate fields – Title, FirstName and Surname. Sometimes we want to join the words together, for example, for sending out an invoice. Joining two or more words together into one string is called concatenation.
The CONCAT function was introduced in Excel 2016 and replaces the earlier CONCATENATE function.
The syntax for this is CONCAT(text1, [text2],…) where text1, text2 etc are the strings you want to join together.
We can concatenate a space character within the CONCAT function as follows:
=CONCAT(A2,” “,B2,” “,C2)
Another way of typing this is =A2&” “&B2&” “&C2.
With the new TEXTJOIN function, the same process would look something like this:
=TEXTJOIN(” “, TRUE, A2:C2). Even more usefully, the TEXTJOIN function allows you to ignore empty cells.
The syntax is:
TEXTJOIN(delimiter, ignore_empty, text1, [text2],…)
The delimiter is the separator required between each text string and needs to be enclosed in double quotes.
If you want to ignore empty cells in a range, then ignore_empty should be True, otherwise False.
Text1, Text2 etc are the parts of text you want to join and can be actual text or a cell range.
If you were using a Slicer in an Excel pivot table, the TEXTJOIN function would allow you to create a title for the table.
If I remove the Grand Total and then adapt my slicer my title will change accordingly.
Find out more about our Microsoft Excel training in the London area.
Find out more about our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.