Joining Text with Ampersand

Ampersand (&) has been used to join text for as long as I can remember.  Simply put you place the & between text you wish to join.

If we are hard-coding 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&" "&C2 

and 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&"-"&C2 

would return Miss-Amber-Guilfoyle

Joining Text with CONCATENATE

CONCATENATE has also been around a long time now and Microsoft are pushing the use of CONCAT instead.  However, I still want to show you how to use this function.

The syntax for CONCATENATE is

=CONCATENATE(text string 1, text string 2…)

As with Ampersand If we are hard-coding 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

Joining Text with CONCAT

CONCAT is very like CONCATENATE

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

Joining Text with TEXTJOIN

TEXTJOIN is an amazing addition to Excels suite of text functions.

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 statement, it can make an extremely powerful lookup.  Stay tunes and we will cover that in a later tutorial.