Joining text in Excel has been a feature that’s around a long time. However as Excel continues to improve, so does it core functions and joining text has never been simpliar.
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.
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 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
Joining Text with CONCATIONATE
CONCATIONATE 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
=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
Joining Text with CONCAT
CONCAT is very like CONCATIONATE
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 statetment, it can make an extremely powerful lookup. Stay tunes and we will cover that in a later tutorial.
Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox
SIGN UP NOW
If you liked this posts – Simplifying Joining of Text in Excel – Please do share. The share buttons are just below the comments!
This blog is powered by the STEEM blockchain using Steempress.io as the integration tool. To comment on this post you must have a STEEM account. You can use this link to register for a STEEM account.
Posted from my blog with SteemPress : http://theexcelclub.com/simplifying-joining-of-text-in-excel/