The easiest way to concatenate a range in Excel is the TEXTJOIN function, which joins a whole range of cells into one cell and lets you pick the separator. If you don’t need a separator, the CONCAT function does the job with a shorter formula.
You’d think the CONCATENATE function would handle this too, but it doesn’t work on a range. In this tutorial, I will show you how to combine multiple cells with TEXTJOIN and CONCAT, plus two methods for older Excel versions that don’t have these functions.
Follow along with the example file
Concatenate Excel Ranges.xlsx
Why the CONCATENATE Function Doesn’t Work on a Range
Before we get to the methods that work, let’s look at why the obvious formula doesn’t.
In its basic form, the CONCATENATE function can join 2 or more text 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’)
You can get the same results with the ampersand (&) operator, for example =A1&A2.
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.
Below is a list of five words that make up a sentence, and I want to combine them with the CONCATENATE function in column B.
Here is the formula you’d probably try first:
=CONCATENATE(A2:A6)
Instead of one combined sentence, you get each word back in its own cell in B2:B6. This happens because the CONCATENATE function handles each cell in the range separately, and in Microsoft 365 and Excel 2021 or later, those results spill into the cells below.
In Excel 2019 and earlier, the same formula shows a single word, such as Today, and doesn’t combine the cells either. So you need a different approach.
Concatenate a Range with a Separator Using TEXTJOIN
Let’s start with the simplest method, as long as your Excel version has it.
The TEXTJOIN function, as the name suggests, can combine the text from multiple cells into one single cell. It also allows you to specify a delimiter, which is just another name for the separator that goes between each value.
The TEXTJOIN and CONCAT functions work in Excel 2019, 2021, 2024, and Microsoft 365, on both Windows and Mac.
If your version doesn’t have them, you can skip to the F9 method or the VBA method.
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 for more text strings or cell ranges. You can use up to 252 text arguments in total, counting text1.
Below is a list of five words, and I want to combine them into one sentence, with a space between the words, in cell C2.
Here is the formula that will do this:
=TEXTJOIN(" ",TRUE,A2:A6)
In the above example, a space character is specified as the delimiter, and the formula combines the text strings in A2:A6 to give you Today is a good day. You only need one formula, and the result updates if any of the words change.
If you want, you can use other separators such as a comma or hyphen by changing the first argument.
You can read more about the TEXTJOIN function here.
Also read: Start New Line In Excel Cell
Concatenate a Range with Commas and Skip Blank Cells
Now let’s look at a comma-separated list, because this is where blank cells start to matter.
Below is a seating list where seat 5 is still empty, and I want a comma-separated list of the guests in cell D2.
Here is the formula:
=TEXTJOIN(", ",TRUE,B2:B10)
The delimiter here is a comma followed by a space, so the names don’t look jammed together. The TRUE argument tells the function to ignore empty cells, which is why the empty seat doesn’t leave a gap in the list.
Now let’s see what happens if you change TRUE to FALSE, this time with the result in cell D5:
=TEXTJOIN(", ",FALSE,B2:B10)
Excel now keeps the empty cell as an item in the list, so you get two commas in a row between Madison and Chelsea. In most cases, you want TRUE.
If you’re joining a very long column, there’s one limit to watch out for. The TEXTJOIN function can return up to 32,767 characters (the most a single cell can hold), and going over that gives you an error instead of the joined text.
Also read: How to Combine Two Cells in Excel
Concatenate a Range Without a Separator Using CONCAT
If you don’t want anything between the values, the CONCAT function is the shorter option because you only give it the range.
Below is the same list of five words, and I want to join them with no separator in cell C2.
Here is the formula:
=CONCAT(A2:A6)
Doing this would combine the range of cells into one cell. Note that since we don’t use any delimiter (such as a comma or space), all the words are joined without any separator, so you get Todayisagoodday.
The CONCAT function has no argument for a separator. You may see =CONCAT(A2:A6&", ") suggested as a workaround, but it leaves an extra comma and space at the end of the result, so TEXTJOIN is the better choice when you need a separator.
CONCATENATE Excel Range in Older Versions Using F9
If your Excel version doesn’t have the TEXTJOIN or CONCAT functions, you can still make the CONCATENATE function work with a little trick. You use the F9 key to turn the range into a list of values, and then give that list to CONCATENATE.
Below is a list of five words, and I want to combine them with no separator in cell C2.
Here are the steps to concatenate an Excel range without any separator:
- Select the cell where you need the result (C2 in this example).
- Go to the formula bar and enter
=TRANSPOSE(A2:A6), but don’t press Enter yet.
The TRANSPOSE function turns the column into a row, because the values in a row come out separated by commas, and that’s what CONCATENATE needs between its items.
- Select the entire formula and press F9 (this converts the formula into values).
Based on your regional settings, you can also try =A2:A6 (instead of =TRANSPOSE(A2:A6)). This helps when the values after F9 aren’t separated by the same character your formulas use between arguments.
- Remove the equal sign and the curly brackets from both ends, so only the words in double quotes and the separators between them are left.
- Add
=CONCATENATE(to the beginning of the text and end it with a round bracket).
- Press Enter.
This gives you the following formula in cell C2, and the result as Todayisagoodday:
=CONCATENATE("Today","is","a","good","day")
To add a separator, add &" " to the end of the formula in step 2. This adds a space after every word, including the last one, and that’s why there’s an extra step below.
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 by changing the text in the double quotes.
Here are the steps to concatenate an Excel range with space as the separator, this time in cell C5:
- Select the cell where you need the result (C5 in this example).
- Go to the formula bar and enter
=TRANSPOSE(A2:A6)&" ", but don’t press Enter yet.
- Select the entire formula and press F9 (this converts the formula into values).
- Remove the equal sign and the curly brackets from both ends.
- Delete the extra space after the last word, so “day ” becomes “day”.
- Add
=CONCATENATE(to the beginning of the text and end it with a round bracket).
- Press Enter.
This gives you the following formula in cell C5, and the result as Today is a good day:
=CONCATENATE("Today ","is ","a ","good ","day")
If you skip step 5, the result still looks right, but it has a hidden space at the end that can cause trouble later, for example when you compare it with other text. With a comma as the separator, you’d see the extra comma.
Also note that the CONCATENATE function accepts up to 255 items and 8,192 characters in total, so the F9 method won’t work on a range bigger than that.
Important: The F9 step turns your cell references into fixed text. If you change a word in column A, this formula won’t update, so you’d need to repeat the steps.
If you need the result to stay linked to the cells and your list is short, enter the below formula in the cell where you need the result, and add a cell for each extra word:
=A2&" "&A3&" "&A4&" "&A5&" "&A6
CONCATENATE Excel Ranges Using VBA
If you’re using an older version and need this often, a custom function is a better option than repeating the F9 steps. Unlike the F9 method, it also stays linked to your cells.
VBA is the programming language built into Excel, and you can use it to add your own function that works in a worksheet like any other.
Below is the code for a 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:
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 FunctionThe line with the Left function removes the separator that gets added after the final value, so this works with a separator of any length, such as a comma followed by a space.
The example file is a regular .xlsx file, so it doesn’t include this code. You’ll need to add it to your own workbook with the steps below.
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 for your workbook in the Project Explorer pane and go to Insert and select Module.
- Copy paste the above code in the module code window.
- Close the VB Editor.
Now you can use this function as any regular worksheet function in Excel. For example, with the five words in A2:A6, this formula gives you Today is a good day:
=CONCATENATEMULTIPLE(A2:A6," ")
This function doesn’t skip empty cells, so a blank cell in the range gives you two separators in a row.
To keep the function in your file, save the workbook as a macro-enabled workbook (.xlsm).
So these are the ways you can concatenate a range in Excel, with or without a separator. I hope you found this Excel tutorial useful.
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!
Other Excel Articles You May Also Like:
- 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
- Add Parentheses Around Text
- Combine Two Columns in Excel (with Space/Comma)
- CHAR guide
- ARRAYTOTEXT Function in Excel
- Text.Combine Function in Power Query M
thank you so much bro!! from the Philippines
For some reason the formula =@CONCATENATEMULTIPLE(A1:A5,” “) OR =CONCATENATEMULTIPLE(A1:A5,” “) in my own spreadsheet. I receive a #NAME error. Only work in your downloadable example – Why would this be happening?
textjoin is awesome
Thank you very much for this tip. As others have said, it solves the =CONCANT issue of time.
I use this daily within Maximo to turn a column of WO #s into a row with a comma and space between each. I then can paste them into a program for modifications.
Textjoin = No more Concatenation torture on many columned joins. Thanks!
Wow this is supremely helpful, saved me A LOT of time! Thanks man.
Your macro just saved my life! Thank you
I. Love. You.
Hy!
Can someone help me with this formula or similiar please?
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
In case that you have values
Name1 Name2 Name3
0 1 2
in a range A1:C2, than you can insert formula
=IF(A2=0,””,TEXTJOIN(” “,,A2,A1))
into cell A3 and fill it to the right two cells.
Then you will get
(Empty cell),1 Name2, 2 Name3
in cells A3:C3.
Thank you!
Thanks a lot . It worked great. I have 1000s of multiple “|”delimited rows and this function did a great job. God bless you .
Thanks, very helpful! One suggestion is to strip off the number of characters in the separator (accommodates separators with multiple characters, e.g. a comma with a space, like “, “).
CONCATENATEMULTIPLE = Left(Result, Len(Result) – Len(Separator))
This is a more generalized solution to Rowan’s specific empty string (“”) seperator fix. Well done and thanks for sharing.
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)
Thanks a lot for the code and the explanation. Great function 🙂
Hi, looking for a VBA code to concatenate the complete row like (A1:A25 in A26). what is the easy way to do it…!!
Many thanks for Multiple option – much appreciated
Concatenatemultiple is fantastic! Only limitation I noticed is if you don’t have a separator (using “”) then it cuts off the last value of the text. So with a small if function it was corrected:
If Separator = “” Then
CONCATENATEMULTIPLE = Left(Result, Len(Result))
Else
CONCATENATEMULTIPLE = Left(Result, Len(Result) – 1)
End If
Thanks for providing the fix. I ran into this.
The CONCATENATEMULTIPLE code works well. What about when the number of cells to concatenate is variable? What would this code look like?
Hi,
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
This could be done much simpler with VBA, but also have many solutions with Excel embedded formulas:
Lets suppose you have the following values in column A:
Project1 Mike
Project1 Neal
Project1 Peter
Project2 Mike
Project2 Neal
Project2 Peter
First, add one row above all and leave it empty, so the content starts with cell A2.
Second, add formula =IF(ROW()=2,LEFT(A2,8)&”, “&RIGHT(A2,LEN(A2)-8-1),IF(LEFT(A2,8)=LEFT(A1,8),B1&”, “&RIGHT(A2,LEN(A2)-8-1),LEFT(A2,8)&”, “&RIGHT(A2,LEN(A2)-8-1))) to cell B2 and fill it down the column B.
For additional clearing add formula =IF(ISNUMBER(FIND(B2,B3)),””,B2) to cell C2 and fill down the column C.
Changing delimiter can be done by changing every “, ” in the first formula.
If your shared word is not “Project1”, you would have to change every 8 (the length of the word Project1) in the first formula with the length of another word.
Thanks, nice little function. Also adapted to use the ‘Len(Separator)’ value:
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
This is a great post and will save me tons of time. Just one query, how would i set up the VBA code to ignore #N/A entries? I want to run the code from a pivot table and the number of results from the pivot changes each time?? Any help would be great. Thanks…..
=IFNA(TEXTJOIN(…..
I think I should clarify my suggestion.
=IFNA(TEXTJOIN(” “,0,$A$1:$A$6),””)
It’s great for getting rid of #N/A if they appear.
There are references on it’s uses on other sites, but it basically is an IF statement that is triggered when #N/A is true, else “”. You can substitute whatever you want to the second part instead of “”. This TEXTJOIN is really useful, and I’ve been using it ever since I stumbled across it.
Perhaps you could implement the TEXTJOIN function from Google Spreadsheet. Here’s my implementation:
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
Thank you for these solutions!
I would have like to alter the VBA method as to avoid empty cells (i.e. so the user can select a whole column, yet concatenate only non-empty cells).
I know it is not trivial – but it would help to make this a more robust function.
Hi,
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?
I am looking to do this same thing. Have you figured out how to do this?
One of the solutions without VBA is submitted above.
Another solution without VBA is:
Enter formula =COUNTIF(A:A,”?*”) to cell C1 (counts number of cells with text in column A)
Enter formula =COUNTIF(B:B,”>0″) to cell C2 (counts number of cells with numbers >=0 in column B)
Enter formula =INDIRECT(ADDRESS(QUOTIENT(ROW()-1,$C$2)+1,1)) to cell D1 and fill down until it starts giving zeros.
Enter formula =D1&INDIRECT(ADDRESS(COUNTIF(D$1:D1,D1),2)) to cell E1 and fill down until it has values in column D. Your solution should be in column E.
PS: Formula in cell C1 is given just in need for making pairs with textual data.
Third solution without VBA (in one line) could be done with formula =INDIRECT(ADDRESS(QUOTIENT(ROW()-1,COUNTIF(B:B,”>0″))+1,1))&INDIRECT(IF(MOD(ROW(),COUNTIF(B:B,”>0″))0,ADDRESS(MOD(ROW(),COUNTIF(B:B,”>0″)),2),ADDRESS(COUNTIF(B:B,”>0″),2))) in cell C1 and fill it down until it starts giving superfluous solutions. In case that you are pairing text in column B, change all COUNTIF(B:B,”>0″) in formula with COUNTIF(B:B,”>0”) .
* Correction:
In case that you are pairing text in column B, change all COUNTIF(B:B,”>0″) in formula with COUNTIF(B:B,”?*”) .
Copy values from column A to column C, beginning from C2:C4. Copy values from column B to column D, beginning from D1, but with Paste_Special>>Transpose, so everything start looking as a empty table with letters for rows and numbers for columns. Now select cell D2 and enter formula =$C2&D$1 (dollar signs must be on that places). Now fill in formula till the end of the rows and columns. Now select all 12 values and copy it without moving the selection, then paste>>paste_values. Now you have to place values in one column. Open new sheet, copy 12 values, select B1 and Paste_Special>>Transpose. Now insert formula =IF(ROW()*1/4=INT(ROW()*1/4),ROW()*1/4,INT(ROW()*1/4)+1) into cell A1. The most important thing is number 4, because it is related with number of rows filled wit values. ***In case of 50 rows with data, formula would be =IF(ROW()*1/50=INT(ROW()*1/50),ROW()*1/50,INT(ROW()*1/50)+1).*** Fill down formula (with fours) 12 times (because you have 12 values). Now insert formula =INDIRECT(ADDRESS(ROW()-(A5-1)*4,COLUMN()+(A5-1),4)) in cell B5 and fill down till B12. The most important in this formula is that FIRST number 4 has same role as in previous, and A5 is there because we put formula in B5.***In case of 50 rows with data formula would be =INDIRECT(ADDRESS(ROW()-(A51-1)*50,COLUMN()+(A51-1),4)) *** Number 4 at the end of the formula is relative address and is not related to your number of rows.*** Column B is one of the solutions without using VBA.
Apparently TEXTJOIN is associated with Office 365, NOT Excel 2016.
TEXTJOIN() is NOT available on my desktop version of Excel 2016.
Hey Cornan.. You’re right! I have edited the tutorial accordingly.
Hi Sumit, im in need to of 2018 leave tracker template. Could you share updated excel version to adriana.galiano@gmail.com
Thx for this, perfect replacement for the MCONCAT formula from the now defunct (for anyone on 64bit OS) morefunc add-in
Cool! I was finding for auto update concatenate. Thanks for VBA code.
This is great – thanks!
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.
… and allows even an empty string as a separator. A nasty little bug 😀
This completely saved my ass today creating contact list spreadsheets to import elsewhere!!!!
Excellent! This did exactly what I needed it to in combination with Dynamic Ranges.
Theres a bug in the code. ExcelConcatenate is not equal to CONCATENATEMULTIPLE you should set CONCATENATEMULTIPLE =
Thanks for sharing such wonder ful trick.
How do you do the reverse, plz suggest.
Hello Alok.. By reverse, you mean to split these words into separate cells? You can do this by using text to column. Have a look at #7 in this – http://trumpexcel.com/2014/08/clean-data-in-excel/
Very Very time saving & Interesting brother, Nice tips
Thanks for commenting.. Glad you liked it 🙂
Can you add sample document?
Thanks…
That is a really cool solution. Will save lots of time. What is the more advanced method that automatically removes the curly brackets?
The more advanced way would be to use two cells. In one cell you would use the F9 key and get the hard coded values, and in another you can have a formula that would automatically remove curly brackets (using replace/substitute). You can go that way if you want this to be partially dynamic. But i would say the one mentioned in the article is easier and faster way.
Very cool. I always had this problem.
Thanks for the comment buddy.. glad it helped 🙂