Concatenating text from multiple cells in Excel into one cell has always been possible but used to be quite a manual task that involved a lot of steps, and the text would not update automatically as new values were added to individual cells.
Using TEXTJOIN it is now much quicker and easier to concatenate text from multiple cells into a single cell.
Download File
If you would like to follow along download the attachment below.
Concatenating Cells Using the Ampersand
Using cell references and the ampersand sign (&) it is possible to join together values from multiple cells into one cell.
Using punctuation and quotation marks along with the ampersand whole sentences or groupings of text in multiple cells can be made.
While this will concatenate the cell values into one cell and separated them out with punctuation, if there were many more than three values this would take a long time to do, and any time the range was extended the formula would need to be manually updated.
What is TEXTJOIN
TEXTJOIN is a text function in Excel that combines multiple text strings into one, separated
by a specified delimiter.
It is simpler to use and offers more flexibility than concatenating with ampersand signs.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
delimiter: The character(s) to insert between text items (e.g., ", ", "|", "-").
ignore_empty: A Boolean value (TRUE or FALSE). Set to TRUE to exclude empty cells.
text1, text2, …: The text strings or ranges to combine.
Using TEXTJOIN to join together text from one array
Using the same data from above, TEXTJOIN allows for text in multiple cells to be concatenated and separated by a common delimiter (punctuation, symbol etc), by entering the delimiter you want to use and selecting the range with the text you want to concatenate.
Delimiters will need to be wrapped in quotation marks to be applied in the function.
Using TEXTJOIN to join together text from more than one array
If you have two sets of text that are in different cell ranges you can use the optional criteria in the function to join these together.
Entering values in text1 is compulsory, but there are options to add values in text2, text3 etc.
All ranges entered in the optional text values will be concatenated together with the first text entered in the compulsory text1 box.
Dealing with Empty Spaces
So far both examples using TEXTJOIN have used TRUE in the ignore_empty segment, as there have been no empty values in the arrays selected it could also have been left blank.
If the array selected has a blank value in it, then using TRUE will ignore the blank value in the joined text output.
Using Tables to Automatically Extend Text Output
When using TEXTJOIN on an array the text will not automatically update if you add text outside the array.
If you need the output of the function to continuously update as you add further values then tables can be used to achieve this.
To do this first convert your dataset to a table by clicking on a cell in the dataset and pressing Ctrl + T.
Now that the range has been converted to a table and named ‘Months’ it can be used in the TEXTJOIN function to dynamically updated the function as the table grows.
Conclusion
Concatenating text using the ampersand can be manual and will not update automatically if you need to add further text.
Using TEXTJOIN you can select a range to join, as well as a delimited to separate out your text, and if your text to join is formatted as a table you will be able to automatically update the joined text by adding additional values to the table.
Comments