Combining Text With The CONCAT and TEXTJOIN Excel Functions
Hey everyone! Chris here and I want to introduce you to a new Excel blog called Spreadsheeto. Bloggers Mikkel and Kasper hail from Denmark where they have been successful in providing online training to the Danish community. With the birth of their new website, Spreadsheeto.com provides English-based training aimed at teaching the entire world! They have offered up their expertise today to teach us a couple of brand new formula functions that were released in the most recent update of Excel 2016. Enjoy!
The Art Of Combining Text
There are a variety of reasons why you might want to combine various cell values into a single value. You might be trying to create a unique ID to perform a lookup or simply wanting to combine location information such as a city with a country. The Microsoft Excel developers have recognized the importance of joining our data inside of Excel and have finally added two more extremely useful functions that we can incorporate into our arsenal of text manipulating skills. In this article, you will learn the basics of combining text with these brand-new functions, and hopefully, you gain some insight into how you can use them inside your spreadsheet work on a regular basis.
AND DON’T WORRY…If you are currently using a version of Excel that was released prior to Excel 2016, in the last section of this very article you will learn how you can get your hands on these functions in any version of Excel!
The New Concatenating Function, CONCAT
CONCAT() is an exciting new function available in the latest versions of Excel (Excel 2016, Excel Mobile, and Excel Online). While its predecessor CONCATENATE() is still available in newer versions of the application, it is recommended that you use CONCAT() instead. The CONCAT() function accomplishes everything CONCATENATE() has in the past but now offers some new additional flexibility. More about that in a moment.
First, let’s discuss the basics of what CONCATENATE() actually does. If you have ever had data in multiple cells that you needed to join, CONCATENATE() has most likely been your answer. However, it is not the only method by which you can do this in Excel. Take a look at the example below. There are two cells with data associated with a person’s name.
We could use the CONCATENATE() function to derive the full name, but to be honest, it’s just as simple to use the ampersand sign (ie &) to join the contents of these cells.
Notice that it is necessary to place a space value (ie ” “) between the contents of Cell A2 and Cell B2. This is in order to obtain the result of a joined string with a space between the first and last name. Now let’s see the same outcome but now by using the CONCATENATE() function.
Clearly, from these examples, you can see that the same results can be accomplished by using different methods. In fact, you might argue that the use of CONCATENATE() is the more complicated of the two methods shown here due to the length of the characters you need to write.
The syntax of the CONCATENATE() function is =CONCATENATE (text1, [text2], …). At its most basic, the function will allow you to join the contents of multiple cells as we saw before by placing a comma between each string of text whether it is a cell reference or literal text.
Before we move on to the exciting new capabilities of CONCAT(), let’s take a quick look at how you can expand the joining of multiple cell contents in another practical sense by placing literal text in the string to be joined. Consider the following data.
Our objective here is to create a formal sentence that analyzes the sales results for the month. In this case, we will not only insert the contents of the cells in our CONCATENATE() formula but also the literal text creating the sentence. The result we aim to get should read as follows:
We saw 24% growth with sales reaching $105M.
In the explanation below, notice the insertion spaces added to the inserted literal text (ie “We saw “). Whatever you insert in the function between double quotes will be output to your resulting string.
Pro Tip: If you need to add quotes into your sentence, you can use the formula =CHAR(34) to create a double quote character. For example = CHAR(34) & “Sales” & CHAR(34) would output a result of “Sales”.
Recall from our first example that the CONCATENATE() formula joins the literal contents of the cells we enter into the formula. Therefore, be aware that if there are no spaces between entries, you will need to add them to your formula to compensate if you want spaces in between.
At this point, you may be thinking “this is all great, but I thought we were going to look at the CONCAT() function”. Good point! So let’s move on.
Introducing The Brand New CONCAT Function
The main limitation of the CONCATENATE() function that CONCAT() has now addressed is we as users can now select a range of cells to join while previously we were limited to referencing a list of single cells. Let’s take a look.
This is a very powerful new capability, especially if you have a lot of columns of data to join. Further, notice in the example above that on row 3 there is no data in the “Middle” field (column B) yet CONCAT() completely skipped it and joined the next cell into the range.
This specific example using names as the string data to join does raise an interesting question: what do we do if we need spaces (or other characters) in between the data we are joining? While CONCAT() does not provide us with a direct method for this, there is now another function that can assist us with this.
Adding Space Delimiters With The TEXTJOIN Function
Referring to the previous example, we can use a different function called TEXTJOIN() to incorporate space delimiters (characters that separate a list of words) into our full names. Let’s first cover the 3 inputs of TEXTJOIN():
- Delimiter – This is a designated character or string of characters that will be inserted in between each “Text” or “Cell” input provided.
- Ignore_Empty – This is a TRUE or FALSE input that asks if you want to ignore blank values. If FALSE, you run the risk of have two delimiter characters next to each other (for example, a result of “Apple,Orange,Banana” could end up being “Apple, Orange,,Banana”)
- Text – the text item(s) to be joined. This can be a text string, or array of strings (such as a range of cells).These are the same exact inputs you would use with the CONCATENATE() or CONCAT() functions.
Now that you have an idea of what inputs go into using the TEXTJOIN() function. Let’s go back to our name example and try to combine the First, Middle, and Last names so that they read intuitively with spaces.
You can see that we delimited each cell’s text value with a space and ignored any blank values (ie Cell B3). Now, what would the results look like if we changed the Ignore_Empty input to have a FALSE value?
Let’s look at another example where we delimit with commas so you can better see the impact.
As highlighted in the above image, the blank value in cell B3 gave a result of back-to-back delimiters (,,). Depending on your situation this may be what you want, but in most cases, you will likely want to keep the Ignore_Empty input set to TRUE.
VBA User Defined Functions For Excel Versions Prior To 2016
If you do not have Excel 2016, there is still a way you can take advantage of these functions. Below is the VBA coding that will mimic the functionality of CONCAT and TEXTJOIN. You can embed this VBA macro code inside your spreadsheets and automatically be able to call them from your formula bar just like a normal built-in spreadsheet function.
To add these functions, use the keyboard shortcut ALT + F11 to open up the Visual Basic Editor. Once inside the editor, you will want to
- Right-click on the name of the file you are currently using
- Go to Insert
- Click Module
- In the white space, paste the code
- Exit out of the Visual Basic Editor window
- Save your file
Then start typing in the function in your formula bar and you should see it appear inside the formula suggestion box!
The CONCAT Function
Public Function CONCAT(ParamArray Text1() As Variant) As String
'PURPOSE: Replicates The Excel 2016 Function CONCAT
'SOURCE: www.TheSpreadsheetGuru.com
Dim RangeArea As Variant
Dim Cell As Range
'Loop Through Each Cell in Given Input
For Each RangeArea In Text1
If TypeName(RangeArea) = "Range" Then
For Each Cell In RangeArea
If Len(Cell.Value) <> 0 Then
CONCAT = CONCAT & Cell.Value
End If
Next Cell
Else
'Text String was Entered
CONCAT = CONCAT & RangeArea
End If
Next RangeArea
End FunctionThe TEXTJOIN Function
Public Function TEXTJOIN(Delimiter As String, Ignore_Empty As Boolean, ParamArray Text1() As Variant) As String
'PURPOSE: Replicates The Excel 2016 Function CONCAT
'SOURCE: www.TheSpreadsheetGuru.com
Dim RangeArea As Variant
Dim Cell As Range
'Loop Through Each Cell in Given Input
For Each RangeArea In Text1
If TypeName(RangeArea) = "Range" Then
For Each Cell In RangeArea
If Len(Cell.Value) <> 0 Or Ignore_Empty = False Then
TEXTJOIN = TEXTJOIN & Delimiter & Cell.Value
End If
Next Cell
Else
'Text String was Entered
If Len(RangeArea) <> 0 Or Ignore_Empty = False Then
TEXTJOIN = TEXTJOIN & Delimiter & RangeArea
End If
End If
Next RangeArea
TEXTJOIN = Mid(TEXTJOIN, Len(Delimiter) + 1)
End FunctionAnd there you have it, 2 new functions for you to use!
You now know the basics of two functions that can be quite powerful while combining your data. CONCAT() takes the capabilities previously available with CONCATENATE() to an entirely new level of functionality. And TEXTJOIN() builds on CONCAT() to provide even more versatility.
Got any questions or thoughts? Leave a comment below!
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Spreadsheeto