SubscribeHow to add a range of cells in excel – concat()
Excel concatenate() is seriously crippled, it can add 2 or more strings together, as long as they are supplied as separate parameters. This means, when you have a range of cells with text which you want to add up to create a large text, you need to write an ugly looking biggish concatenate() or use ‘&’ operator over and again.
I felt bored enough the other day to write a better concatenate(), one that can accept a range as input and output one text with all the contents of the input range. What more you can use this to delimit the input range with your own favorite character.
For example, if each of the 7 cells in a1:a7 have “a”, “b”, “c”, “d”, “e”, “f”, “g”, if you want to add all of them up using concatenate you would have to write concatenate(a1,a2,a3,a4,a5,a6,a7) which can be painful if you are planning to do this over a large range or something.
Instead, you can use concat(a1:a7) by installing the UDF (User defined function) I have written. Its nothing miraculous or anything, it just does the dirty job of going through the range for you. If you want to delimit the input range with a comma just use concat(a1:a7,",") to get the out of a,b,c,d,e,f,g Just download the concat() UDF excel add-in and double click on it to install it. If you are little weary of installing UDFs / Macros from third parties, copy past the below excel code in a new sheet’s VB editor and save the sheet as an excel addin (.xla extension)
Added on Aug 26, 2008: I have updated the code, copy it again if you have the old one
Function concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
if cstr(cell.value)<>"" and cstr(cell.value)<>" " then
retVal = retVal & cstr(cell.Value) & dlm
end if
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function
Did you find this useful? Are you looking for some other excel UDFs as well, drop a comment, I am a busy coffee drinker, but between the sips I can whip out ugly looking but functional vb code 
|
|
Posts & Navigation
Tags: add-in, concat, Excel Tips, how to, microsoft, Microsoft Excel Formulas, technology, tips, tricks, udf, VBA |
Trackbacks & Pingbacks
- Pingback by How to count words in a cell using excel functions | Pointy Haired Dilbert - Chandoo.org on July 8, 2008 @ 3:38 pm
- Pingback by Splitting text in excel using spreadsheet formulas | Pointy Haired Dilbert - Chandoo.org on September 8, 2008 @ 3:45 pm
- Pingback by Use Alt+Enter to get multiple lines in a cell | Pointy Haired Dilbert - Chandoo.org on December 19, 2008 @ 1:41 am
- Pingback by Excel Links of the Week - Free E-Book Edition | blogging | Pointy Haired Dilbert - Chandoo.org on March 9, 2009 @ 1:32 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
You are a god in Excel!
I bow down to you o master of Excel…
But… how do I use it?
The formula does not appear when i type in ‘=conc…’ in a cell.
@Hypnos .. thanks alot man
I think you need to save the downloaded xla file in your excel add-in folder (just select save as and use the excel add-in as the file type, the folder will be shown in the save dialog automatically), let me know if this doesnt work.
Duh!
It didn’t work, that is why I wrote… give me some credit dude
I used to be in your ITCOM.
hmm.. thats tricky, I read that it works that way, it actually did work that way for me, by saving the xla file in the addins folder, i could see and use the formulas. Btw, the formula may not appear when you type =concat… in the cell, but it works nevertheless. Anyways, can you try pasting the code in a module in your sheet and save that sheet as an addin instead… meanwhile I will investigate why this wouldn’t work…
that was a great lead to me, and thansk a ton. need you help in this problem. in eh above example if a3 has ‘c’ but the actual field length should be 8 how do i make use of the same program to create a fixed length text so that i can export it as *.edi file.
In essence i want to create form a excel sheet an exi file withfixed text format having an option to enter headers
@capstri .. thanks for the comments..
let me see if I can help you…
In the above code you can change the for loop to something like this to get the desired effect.
For Each cell In useThis
retVal = retVal + cell.Value + rept(” “,8-len(cell.Value))
Next
you can replace the “8″ with whatever fixed lenght you have in mind. Also, I have not tested the above code, you may have to replace the rept() with something else if it throws an error. Let me know so that I will help you if I can…
When I add/save the xla file to my addins directory ‘C:\Documents and Settings\colebro\Application Data\Microsoft\AddIns’, i double click on the file to install it. When I enter the command =concat(a4:d4) I get the invalid value (#VALUE!). The ‘Excel-Udf-Concat’ addin is checked iin my add-ins available list. So, I copied your excel code and saved it as an xla file in the add-ins directory, made sure it was ‘checked’ and still nothing. What am I doing wrong? This add-in will save me a lot of time. – Also, what happens when I send my spreadsheet to another user that does not have your add-in, does the concat still work?
@Colebro: Hey.. that is strange, I remember Amit having similar problem. Did you try copying the code and saving it in to your own excel file in a new module? If you do that, then even if the excel file is sent to another person, the function still works… but if you save it as an add-in then the other computer should also have the addin installed.
Let me know if copying the code helps you… otherwise I will investigate in to this further….
@colebro: Looks like this is not an error with the how you add the add-in but with the add-in it self…
My udf would work fine as long as the input range has strings (text) in them, but when the range has numbers in them the udf would throw #value error. Here is a fix…
replace this part of the code with:
For Each cell In useThis
retVal = retVal + cell.Value + dlm
Next
For Each cell In useThis
retVal = retVal + cstr(cell.Value) + dlm
Next
essentially I am force converting each cell’s value to string before creating the concatenated value… this seems to work when you have numbers / dates etc in the cells. Let me know if this helps you…
I must be doing something real stupid because I can not get this to work. I removed the Add-in, changed the statement in the code above and re-added the xla to the add-in. I put a value (either numeric or alpha) and I get an invalid name (#NAME?)
a b c d e
=concat(a1:a6)
#NAME?
BEAUTIFUL – I can’t say anything more. THANK YOU SO MUCH! Now, if the function could just skip blank cells…in other words, if in concat(b1:b8,”; “) there were 3 blank cells, it wouldn’t return something like this
text 1; text 2; text 3; ; ; text 6; ; text 8
but instead would return only
text 1; text 2; text 3; text 6; text 8
I would fall over and get rugburn on my nose. Even just what you wrote is a huge help – joining large cells, and getting real sick of having to join subsets, then aggregate the subsets in another “overall” concat statement.
LOL – might be too practical for us, right???
@colebro: Oops, I missed to respond to this comment, let me see if I can fix this for you…
Function concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
retVal = retVal + cstr(cell.Value) + dlm
Next
If dlm "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function
this should work… if not try saving the function and your spreadsheet… often excel takes sometime to figure out that the udf is now present. let me know if this doesnt help…
@SheilaC: Welcome to PHD, thanks for the awesome comments… I am happy you found this really useful…
I have added an if condition in the loop to only add the cell if the contents are not blank… hope this helps you in getting that rugburn
Function concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
if cstr(cell.value)<>"" and cstr(cell.value)<>" " then
retVal = retVal + cstr(cell.Value) + dlm
end if
Next
If dlm "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function
let me know if this doesnt help…
@SheilaC: you may want to replace the dirty quotes in the code with proper double quotes…
…Sheila would like to reply to Chandoo – unfortunately she is in the hospital with grade 1 rugburn…in fact, they are extracting rug from her nostrils as we speak.
OMG Chandoo – if you were here – I WOULD HUG YOU SO BAD YOU’D SQUEEK!
Major help like you have no idea. 
Sheila
Other quick edit
change line:
retVal + cstr(cell.Value) + dlm
to:
retVal & cstr(cell.Value) & dlm
Otherwise Excel sends an error when cell.value is a number
@SheilaC : Hope you are alright
, I am happy this helped you.
@MikeP : thanks for pointing it out, I have changed the code to include your suggestion.
it was a great help. thanks!!
Very interesting but my dilema is I have a formula in each cell that says VLOOKUP((TEXT(O3,”mm/dd/yy”)&A9),Monthly_Data,2) but when the string is not found (which is very likely the majoriity of the time) the result is #VALUE. Can I use an IF statement that says if the string is not in range enter a 0?
@Navneet … Thanks for the comments
@EStout … hmm, I guess you can change the UDF to include a condition to check if the cell has an error then skip it.
here is how:
if iserror(cell.value) = false and cstr(cell.value)“” and cstr(cell.value)” ” then
retVal = retVal & cstr(cell.Value) & dlm
end if
Another way to get this is to modify your vlookup to include some error handling, like
if(iserror(VLOOKUP((TEXT(O3,”mm/dd/yy”)&A9),Monthly_Data,2)),0,VLOOKUP((TEXT(O3,”mm/dd/yy”)&A9),Monthly_Data,2)).If you are using excel 2007 you can try is the iferror function.
Thank you so much!!! It worked.
you have no idea how much time this saved me!!!! thank you so much! i merged 1395 cells into one!
hi chandoo,
a feedback on this UDF in XL2007.
I’m facing problems exactly as colebro’s (August 9, 2008).
getting #VALUE! when the range contains numbers (e.g. A, B, 1, D, E)
Is this UDF XL2007-compatible?
ok, please ignore my previous post …
the codes work when embedded in the target sheet.
if i dump the XLA into Addins folder, it won’t work (properly).
also, if the cells have errors (#value!, #div/0!), it gives me “Error 2007″
ok, please ignore the previous post. ..
i got the codes work when embedding it in target sheet.
if i dump the XLA into Addins folder, it won’t work (properly)
Also, if the cells contain errors (#value!, #div/0!), concatenated cell shows as “Error 2007″
@Cybpsych: cool… as you can see, the udf is fairly straightforward and simple. I could have made it complicated, but I thought of keeping it simple to handle text concatenation without worrying about all exceptions.
@Morgan: You are welcome. I am happy you found this useful
Help I cannot get the last code to work. I get an error from the following code:
if cstr(cell.value)“” and cstr(cell.value)” ” then
@Daniel: you need to insert = between cstr(cell.value) and “”. Same for the next one too. Let me know if you still face a problem. I will try to upload code in a downloadable format.
Ok, I am at my wits end. I have been given a template for my workbooks that I have to use. They include embedded images for headers/footers.
Ex-post-facto development of a complex document, I have to figure out how to incorporate these two images as the left and right headers. However? I use VBA code to generate all my header/footers prior to printing. The workbook has like 40-something sheets.
I can put the image directly into the left header, however it points to a file location for the image on my hard drive. This will cause the logo not to show up when the spreadsheet is downloaded off of the storage location it is placed into. Which is a violation of an official document. rrrrrrrrr!
I tried to link my image to a cell, but there is not a feature to do that in Excel. I can size the image with a row, but it won’t show the image when I use code to refer to that cell – however, text in the cell does show up so it is including what Excel sees “in” the cell.
I even tried (grasping here) using a comment with an image background.
Repeat rows does not “see” the picture either.
What can I do????
@SheilaC: which version of excel are you using? In excel 2007, when I have an image in some rows and define those rows to be repeated at top while printing (from ribbon > page layout > print titles) they are properly repeated for each of the pages on the output.
Since you say you are already using VBA to handle some stuff, may be you can think of an out of box solution like transporting excel output to word templates with images already in and then sending them to printers… That is if your version of excel doesnt support images in header rows…
I am using Office 2003. GOD I HATE 2007’s ribbons! Why cain’t we just keep our dang buttons Mama? Like good little mice we’ve learned how to push the lever for our pellet, and now – we have to use something that hogs 1/3 of our screen and requires the “Help” function constantly to figure out how to do stuff!!!
NEways, I can’t figure it out. It is part of our markings requirements to have these images embedded. So – I’m just leaving off the Left & Right header generation statements in VBA (before print routine), and hard putting them in there using the picture icon in the Header & Footer menu.
But I know, if anyone could’ve solved this – it’s you. You are a VBA GOD!!!
LOL. My nose is still flat from my last helping here.
PS> How do I open a new thread?
@SheilaC: you said, you have written some VBA to insert images in to the footer. May be you can change code to insert few rows on the top, insert images there and make the rows repeated. That way images need not be copied along with the file.
PS: Unfortunately we dont have any page where you can ask your questions. The usual process is locate the posts that talk about the topic you need help on, just post a comment on the latest post and you should get the response.
Can you please tell me how to add a description to your above UDF like the one that gets displayed in default functions available in excel 2007? Is it also possible to write a help topic on it?
This blog post (along with its precious comments and the replies) contains the most comprehensive coverage of how to concatenate a range in excel (among all the posts I searched on this topic). Thus, I want to further add some nerdy facts to it. Using your above function I created cell references to put in the two default cell merging options in the excel i.e. (a) CONCATENATE function and the (b) using the ‘ampersand‘ sign ‘&‘. The column A had the data to be merged and column B had corresponding cell references of column A i.e. value in cell B1 was ‘A1′, B2 was ‘A2′ and likewise. I used the Chandoo’s VBA, i.e. =concat(B1:B9,"&"). Using Paste special—>Values and adding an ‘equal to‘ sign i.e. ‘=’ I obtained a formula that would work in excel by default so that there is no need of availability of VBA or the add-in if the file is used on some other systems. Following are some facts that I found,
A formula can have a maximum of 8,192 characters
You can give a maximum of 300 cell references in a single formula using the default "&" (ampersand) sign
You can give a maximum of 30 cell references in a single formula using the default "Concatenate" function (though the function’s help states that it can take 255 cell references, my trial didn’t go beyond 30)
A single cell can hold a maximum of 32,767 characters. Thus, you can re-Concatenate the above results, which will follow the above conditions regarding cell references, into a single cell till you hit the maximum limit of 32,767.
P. P.S.: I don’t know anything about VBA
P.S.: I use MS Excel 2007, you can download my excel worksheet here
P.P.S.: Please do not forget to answer my 2 ques. written at the beginning of this comment
@jUGAD: Welcome to PHD and thanks for such precious comments.
You have found such cool facts about Excel limitations.
Coming to your questions, you can find some material here: http://www.excelforum.com/excel-programming/577764-adding-help-text-to-a-user-defined-function.html and http://www.ozgrid.com/VBA/udf-cat-description.htm
As you can see from the above articles, when you create an UDF you can set description using either VBA or using record macro dialog. I think when you port your UDFs as Add-ins, there will be better control over the dialog etc (not sure though)
Let me know if you learn anything more about this…
PS: I like your passion and enthusiasm about excel…
Hi Chandoo,
As I had already written in my previous comment that I do not know anything about VBA, further, I had already seen your suggested links before asking from you as I could not find them fruitful even after lots of trials from whatever I understood of them.
It would be great if you could show the complete code here so that I can just copy and paste it
This is AWESOME!! I love this add-in. Works great and saves a ton of work pasting in Word, adding characters, etc.
Thank you for doing this!
@David.. you are welcome
This is great!! Have you figured out how to comma or semicolon-delimit the resulting string? That would also be really helpful!
@PJ: The function has an option delimiter parameter, you can pass “,” or “;” to it and it will delimit the values with that.
Thanks, that worked great! You just saved me a ton of time!
Many, many thanks. Saved me a huge effort too!
Also, if you have a range that has nothing in it, you might find the following useful:
Change
If dlm “” Then
To
If dlm “” And retVal “” Then
That way when there is nothing in the range and you have a delimiter specified you don’t get an error when the code tries to subtract the delimiter length from and empty string.
Wade
Great add-in, but it would be even greater if you could add another condition to whas Sheila added. So here goes:
I want to concatenate text in range A1:A100, only if the respective value from range B1:B100 equals to letter “M”.
So A1=”Sheila”, A2=”Chandoo”, A3=”Amer”, A4=”David”, A5=”PJ”, A6=”Ed”, A7=”Leah”, etc.
B1=”F”, B2=”M”, B3=”M”, B4=”M”, B5=”M”, B6=”M”, B7=”F”, etc.
C1=”F”, C2=”M”
I want D1 to be all values from column A where corresponding value from column B equals whatever is in C1 (”Chandoo, Amer, David, PJ, Ed”)
I want D2 to be all values from column B where corresponding value from column B equals to whatever is in C2 (”Sheila”, “Leah”).
Also, this should work without the list being ordered by any of the columns.
I guess the function should take 3 parameters (rangeToConcatenate, rangeToTestCondition, ValueToTestConditionAgainst)
@Amer… you can use a simple IF along with helper column so that you show the value only if the testCondition is “M”. Then pass the new helper column range to concat.
Of course, you can write another UDF, but such a formula becomes less generic..
Thanks a lot for your formula. It flawlessly concatenated a range of 100 fields yesterday!
- Milco
@Amer
You could also use array formulas rather than helper columns. Just change the range’s type to variant so that it will accept an array as input.
My application was to augment the rows of a pivottable (an inventory) with a list of textual codes (identifying the applications for which the item is used) taken from the rows in which the item’s name appears in the source table.
So I use:
{=IF(A5″”,concat(IF(A5=’Lesson Items’!$C$2:$C$250,’Lesson Items’!$A$2:$A$250,”"),” “),”")}
where ‘Items’ is the sheet with the items listed by application, with item names in C and application names in A; and where A5 holds the item name in the current row of the pivottable. I was really frustrated by the lack of ‘concatenate’ as an aggregation function in pivottables, but this method seems to work pretty well.
For those unfamiliar, remember that you don’t actually type the curly brackets “{” and “}”; you type in the formula and hit “ctrl-shift-enter” rather than just “enter”, and Excel processes it as an array formula, marking it as such with the brackets.
Here’s the version of the function I used:
Function Concat( _
myRange As Variant, _
Optional myDelim As String = “” _
) As String
Dim myRetv As String
For Each v In myRange
If v “” Then
myRetv = myRetv & v & myDelim
End If
Next v
If myRetv “” And myDelim “” Then
myRetv = Left(myRetv, Len(myRetv) – Len(myDelim))
End If
Concat = myRetv
End Function
Sorry about the formatting of my last post. I didn’t realize whitespace wouldn’t be preserved.
Hey Chandoo/PHD! I just wanted to comment that this helped me out at work a TON! I spread the knowledge and it’s helped a few others as well.
One question – is there any way to get the current UDF to IGNORE text values?
EXAMPLE:
=concat(A23:A420,”,”)
The intent here is to simply grab each number in all of the cells – except in that range, there are text values too. I created merged cells as a sort of “header” breaking up sections of the sheet. So, I’m getting back…
Week1,Task ID,982,989,1010,2221,Week2,Task ID,2213,3222,Week3,
I want to IGNORE any text values and just have it grab the numbers so I get something like:
982,989,1010,2221,2213,3222
Any help is appreciated! AND! I’ve added your site to my iGoogle. This is awesome