Concatenate a cell range without vba in excel
Joining many text strings in excel is not easy. The Concatenate function accepts only a single cell reference in each argument.
Example,
=Concatenate(A1, A2, A3) + ENTER
To quickly select cells you can press CTRL and click on cells you want to be included in the concatenate function.
Example,
- Select a cell
- Type =concatenate( in formula bar
- Press and hold CTRL button and click cells to be included.
- Release CTRL button
- Type ) in formula bar and press Enter
The above example works if you don´t have so many cells to add. A large range would be tedious to add, clicking on each cell.
Concatenate a cell range
Here is a larger range, A1:A50 with some values.
Let´s concatenate these values:
- Select cell B1.
- Type =A1:A50 in formula bar.
Edit: Depending on your regional settings, try this: =TRANSPOSE(A1:A50) - Press F9.
- Delete curly brackets in formula bar.
- Type =Concatenate( in front of all characters in formula bar.
- Type ) at the end in formula bar.
- Press Enter
Formula in cell B1:
Create delimiting character
It would be great if we could have a character separating values. Here is how:
- Select cell B2.
- Type =A1:A50&", " in formula bar.
Edit: Depending on your regional settings, try this: =TRANSPOSE(A1:A50)&", " - Press F9.
- Delete curly brackets in formula bar.
- Delete last delimiting character.
- Type =Concatenate( in front of all characters in formula bar.
- Type ) after last character in formula bar.
- Press Enter
Formula in cell B2:
Related posts:
Concatenate cell values in excel
User defined function to split words in a cell range into a cell each in excel
Filter duplicate words from a cell range in excel (udf)
Filter unique distinct words from a cell range in excel (udf)
Use a drop down list to extract and concatenate unique distinct values
Recommended categories
Wont work.
A1:A50 will return a row array.
You will need to type =transpose(a1:a50) in cell b1 , say f9, delete the curly brackets and type concatenate
Sam,
Thanks for commenting!
It works with my regional settings in excel 2007.
Oscar, In the English version the row array has a ; as a separator and a column array has a , as a separator.
=A1:A10 give ={"name1";"name2";.....;"name10"} etc
Great tip, Oscar. Surprised that Microsoft didn't fix this in the Excel 2010, but go figure. Sam, I think I had the same problem as you and I was running the evaluate (F9) by selecting just the range and not the full TRANSPOSE(A1:A50) in the formula. If you run the evaluate on just the evaluated range of A1:A50, it will keep the semi-colon separator and not use the comma. But if you run the F9 by selecting the function and the range [TRANSPOSE(A1:A50)], it should work. Awesome tip, I have run into this issue many times before. Glad I now know the fix.
Michael,
Thanks!!
[...] Unfortunately you need some vba code to pull this one off: VBA Join function or maybe not, read this: Concatenate a cell range without vba in excel [...]
Great tip thanks! On Office for Mac, use command+'=' instead of F9
Thanks very much. Especially the extra bit adding a separator. Will save me a lot of time.
I had to play with it a bit in Office 2010, but that F9 trick saved me hours.... Nice job !!!
Damien, Ross and Mark
Thanks!!
Thanks man! Great tip!
Nice.Thanks for your help. Only annoying thing is that the formula is limited on 8592 caracters or something like this. otherwise is just perfect with F9 function. Good job. 10x
There's another way to do it. It's a bit more cumbersome but it updates automatically (which this won't do for me) and you don't need to go through the process of selecting each cell (which can take quite awhile if there are a lot of them).
In a new cell, alongside the ones you will concatenate or anywhere else that there's enough space, set it equal to the first cell. Then in the next cell, concatenate that new cell and the second cell. Now just drag it all the way to the end and they'll all be concatenated. (I did it the other way around from the end to the beginning so the resulting string was at the beginning but it should work either way.
Like I said, it's a bit cumbersome but it updates and you don't have to select all the cells. As a plus, you can add onto it or take away from it simply by dragging without having to edit the formula again.
@Richard,
If you do the repeatd concatenations downward, you can display the last one using this formula (adjust the specified ranges as needed)...
=LOOKUP(2,1/(B1:B65535""),B:B)
Richard,
I believe this post describes the same method:
http://www.get-digital-help.com/2010/08/23/concatenate-cell-values-in-excel/
You can also use an user defined function to concatenate values:
http://www.contextures.com/rickrothsteinexcelvbatext.html
[...] פתרון המשתמש בכלים הקיימים באקסל – קראתי אותו בעמוד הבא. החסרון של פיתרון זה הוא חוסר אפשרות [...]
Great tip! If only I had known this years ago.
You are a genius.
worked great
lets take over the world
Brilliant!
Awesome, you just saved me hours of work!!!!
thanks..gr8 workaround.
This post was sickeningly awesome.
Awesome seriously ... frustration is abated. My work needed I concatenate range of cells with comma. Works amazing. My regional settings needs the =transpose(a1:150)&","
Thanks much
Hello,
thanks a lot for these steps.
It helps do many things faster as other users wrote.
Michal Kalián,
Thanks for commenting!
Genius! excellent
So happy to have found this
Sam and Caz,
I am happy you like it!
My problem was a continuously expanding dataset of term deposits from which some values needed to be extracted to a single text string based on date for inclusion in a report. Let's say the dataset occupied columns A, B, C and D. In column E, I created an IF() function to return the report output, including a delimiter, for that entry if it lay in the wanted date range and a null "" string if it did not. The problem then was to concatenate the results in column E. I solved this by concatenating the result in each row with the value in the subsequent row thus, in cell E1, the formula looks like =CONCATENATE(IF(....),E2) where IF(....) is the formula for the output based on row 1. I then used fill down to replicate this formula in cells (E2:En) and the output in cell E1 then contains all that I need for my report. Works for any number of rows but will become slow if too many rows produce wanted output.
Alan,
Thanks for sharing!
[...] Publisher More from patrick dhungel: Python Miscellaneous kindle Sort Share http://www.get-digital-help.com 3 minutes [...]
What if you had multiples columns which you wanted to concatenate individually?
A1 A2
B1 B2
C1 C2
.. ..
A1, B1, C1 etc
A2, B2, C2 etc
Manually doing F2 F9 across multiple columns would be painful. Is there formula or macro that can automate this?
The point of this page is to do it without a macro (vba) and, short of writing one, I don't think it can be done for multiple columns. As to the pain, the method that I and others have used involves putting a formula in only one cell, the top cell of the leftmost column, then using fill down to replicate it in the other cells of that column. Once that is done, select the column that now contains the formulas and fill right to replicate the whole column for the remaining columns. Unless your spreadsheet is huge, this should be relatively quick and easy. If your columns are not adjacent, you will have to use copy and paste instead of fill right.
Cube,
Rick Rothstein (MVP - Excel) posted a user defined function here:
http://www.get-digital-help.com/2010/08/23/concatenate-cell-values-in-excel/#comment-5003
Great tip, its what Im looking for
I've tried to apply for multiple row but not success. could you kindly guide how to do if it is possible
data as follow:
A B C result
1 100 200 300 100200300
2 400 500 600 400500600
3 700 800 900 700800900
........................
I have a lots of rows, there is any way to apply for multiple rows?
I means, we dont need go to each cell then press F9 then delete curly brackets.
tks so much
DTNAM,
I recommend using Rick Rothstein's (MVP - Excel) user defined function here:
http://www.get-digital-help.com/2010/08/23/concatenate-cell-values-in-excel/#comment-5003
tks so much, I just want to find out any tips to solve it
You should add a nice formula to have cells report their own reference as a string eg:
=CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""),ROW())
Then you can easily create a string of references for further concatenation eg: put my first formula in A1 and copy the following formula into cell B1 and then copy that through to J1. The desired string is then in J1
=CONCATENATE(A1,",",CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""),ROW()))
Two formula, no typing: copy paste x3 Done.
richard,
Thank you for you contribution.
I made a similar post a while ago that concatenates values:
http://www.get-digital-help.com/2010/08/23/concatenate-cell-values-in-excel/