In this article and video below, you will learn how to join text using ampersand, CONCATENATE, CONCAT and TEXTJOIN.
CONCAT and TEXTJOIN are newier functions, there were available in office 365 and are now part of the standard functions of Excel 2019 Standalone.
Looking at some sample data, we are going to look at the use of each function to join the title, first name and last name into the one cell.
If we are hardcoding in the text we must place it between quote marks, however if you are referencing a cell with text, you do not need to use quotation.
Lets say we wanted to join this test with a space between each character
We could use the following formula
=A2&" "&B2&" "&C2and this will return in the one cell Mrs Paula Guilfoyle
If we omitted the " "& and just had
=A3&B3&C3,this will return in the one cell MasterDylanGuilfoyle as we would be joining the text with no space.
We also do not have to use a space. We could split with a – or any other character of your liking
=A2&"-"&B2&"-"&C2would return Miss-Amber-Guilfoyle
The syntax for CONCATENATE is
=CONCATIONATE(text string 1, text string 2…)As with Ampersand If we are hardcoding in the text we must place it between quote marks, however if you are referencing a cell with text, you do not need to use quotation.
=CONCATENATE(A2," ",B2," ",C2)will return Mrs Paula Guilfoyle. Note how this has spaces between it as we have " "to identify the spaces between the referenced cells.
If we leave out " ", we will have no spaces between the text strings
=CONCATENATE(A3,B3,C3)will return MasterDylanGuilfoyle
We can also use other characters to separate the text by placing that character between quotes.
=CONCATENATE(A4,"-",B4,"-",C4)would return Miss-Amber-Guilfoyle
Its syntax is
=CONCAT(text1, text2…)
=CONCAT (A2," ",B2," ",C2)will return Mrs Paula Guilfoyle. Note how this has spaces between it as we have " "to identify the spaces between the referenced cells.
If we leave out " ", we will have no spaces between the text strings
=CONCAT(A3:C3)will return MasterDylanGuilfoyle. Note how this is different to CONCATENATE where we had to reference each cell with text separately. In this case we can use A3:C3 grabbing all the cells at once. With CONCATENATE this would result in #VALUE!
We can also use other characters to separate the text by placing that character between quotes.
=CONCAT (A4,"-",B4,"-",C4)would return Miss-Amber-Guilfoyle
The syntax is
=TEXTJOIN(delimiter, Ignore empty, text1, text2….)Where the delimiter allows you state how each text item is separate
Ignore empty allows you chose between including or ignoring empty cells.
When looking at CONCAT we seen that to separate text with a space or any other character, we had to add something like “-“ between each text time. With TEXTJOIN we only need to define this once.
With this in mind we can replace
=CONCAT (A2,"-",B2,"-",C2)with
=TEXTJOIN("-",TRUE,A2:C2)
And we will be returned with the same value
In the above example we selected ignore empties. To show this in action lets delete the First names from the data set. When we do this we can see that the TEXTJOIN function completely ignores the blank cells
We will now change the formula to include empty cells.
=TEXTJOIN("-",FALSE,A2:C2)
As you can see from the image, by including empty cells we now have two instances of – after the title as the empty cell has been included.
TEXTJOIN as shown in this example is a great way for joining text in a more efficient manor. However by combining TEXTJOIN with IF statetment, it can make an extremely powerful lookup. Stay tunes and we will cover that in a later tutorial.