Excel CONCATENATE function (or the ampersand (&) operator) Excel TEXTJOIN function (new function in Excel if you have Office 365)
In its basic form, CONCATENATE function can join 2 or more characters of strings. For example:
=CONCATENATE(“Good”,”Morning”) will give you the result as GoodMorning =CONCATENATE(“Good”,” “, “Morning”) will give you the result as Good Morning =CONCATENATE(A1&A2) will give you the result as GoodMorning (where A1 has the text ‘Good’ in it and A2 has the text ‘Morning’.
While you can enter the reference one by one within the CONCATENATE function, it would not work if you enter the reference of multiple cells at once (as shown below): For example, in the example above, while the formula used is =CONCATENATE(A1:A5), the result only shows ‘Today’ and doesn’t combine all the cells. In this tutorial, I will show you how to combine multiple cells by using the CONCATENATE function. Note: If you’re using Excel 2016, you can use the TEXTJOIN function that is built to combine multiple cells using a delimiter.
CONCATENATE Excel Range (Without any Separator)
Here are the steps to concatenate an Excel range without any separator (as shown in the pic): Doing this would combine the range of cells into one cell (as shown in the image above). Note that since we use any delimiter (such as comma or space), all the words are joined without any separator.
CONCATENATE Excel Ranges (With a Separator)
Here are the steps to concatenate an Excel Range with space as the separator (as shown in the pic): Note that in this case, I used a space character as the separator (delimiter). If you want, you can use other separators such as a comma or hyphen.
CONCATENATE Excel Ranges (Using VBA)
Below is an example of the custom function I created using VBA (I named it CONCATENATEMULTIPLE) that will allow you to combine multiple cells as well as specify a separator/delimiter.
Here is the VBA code that will create this custom function to combine multiple cells: Here are the steps to copy this code in Excel:
Go to the Developer Tab and click on the Visual Basic icon (or use the keyboard shortcut Alt + F11). In the VB Editor, right-click on any of the objects and go to Insert and select Module. Copy paste the above code in the module code window. Close the VB Editor.
Click here to download the example file. Now you can use this function as any regular worksheet function in Excel.
CONCATENATE Excel Ranges Using TEXTJOIN Function (available in Excel with Office 365 subscription)
In Excel that comes with Office 365, a new function – TEXTJOIN – was introduced. This function, as the name suggests, can combine the text from multiple cells into one single cell. It also allows you to specify a delimiter. Here is the syntax of the function: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
delimiter – this is where you can specify a delimiter (separator of the text). You can manually enter this or use a cell reference that has a delimiter. ignore_empty – if this is TRUE, it will ignore empty cells. text1 – this is the text that needs to be joined. It could be a text string, or array of strings, such as a range of cells. [text2] – this is an optional argument where you can specify up to 252 arguments that could be text strings or cell ranges.
Here is an example of how the TEXTJOIN function works:
In the above example, a space character is specified as the delimiter, and it combines the text strings in A1:A5. You can read more about the TEXTJOIN function here. Have you come across situations where this can be useful? I would love to learn from you. Do leave your footprints in the comments section!
How to Merge Cells in Excel the Right Way. How to Find Merged Cells in Excel (and get rid of these) How to Quickly Transpose Data in Excel. How to Split Data using Text to Columns. The Ultimate Guide to Find and Remove Duplicates in Excel.
I want to use texjoin for one row with results only in non empty cells, but I got result with zeroes. and also I have to combine a column name with data found in particular cell name1 Name2 Name3 row 0 1 2 =1 Name2, 2 name3 is that possible? tnx CONCATENATEMULTIPLE = Left(Result, Len(Result) – Len(Separator)) Note, for some reason in Excel MVBA 7.1, I was unable to apply this code directly. I had to apply the simple math from the second parameter in Len() for the VB editor to accept the code. I.e.: resLen = Len(Result) sepLen = Len(Separator) tmpLen = resLen – sepLen CONCATENATEMULTIPLE = Left(Result, tmpLen) Does anyone know a way to do the following: Concatenate the values of several cells into a single cell and separate them with any delimiter of your choosing. Project Name Result Project1 Mike Project1, Mike, Neal, Peter Project1 Neal Project1 Peter Project2 Mike Project2, Mike, Neal, Peter Project2 Neal Project2 Peter Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String Dim Cell As Range Dim Result As String For Each Cell In Ref Result = Result & Cell.Value & Separator Next Cell CONCATENATEMULTIPLE = Left(Result, Len(Result) – Len(Separator)) End Function Function TEXTJOIN(separator As String, skipEmpty As Boolean, Ref As Range) As String Dim i As Integer Dim tmp As String For Each Cell In Ref If (Cell.Value “”) Then tmp = tmp & Cell.Value & separator End If Next Cell TEXTJOIN = Left(tmp, Len(tmp) – Len(separator)) End Function Does anyone know a way to do the following: I want to combine or concatenate text in every cell in column A with every cell in column B without repeating or flash fill because that won’t do the trick.. Example here: COLUMN A contains: A B C COLUMN B contains: 10 20 30 40 What I want as output in another COLUMN: A10 A20 A30 A40 B10 B20 B30 B40 C10 C20 C30 C40 Anyone an idea how to do this? TEXTJOIN() is NOT available on my desktop version of Excel 2016. Would suggest one change to the VBA code – instead of using CONCATENATEMULTIPLE = Left(Result, Len(Result) – 1), you can use CONCATENATEMULTIPLE = Left(Result, Len(Result) – Len(Separator)); this will allow for multi-character separators. Thanks…
