How to add a range of cells in excel – concat()

Posted on May 28th, 2008 in hacks , Learn Excel , technology - 106 comments

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 :P

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

106 Responses to “How to add a range of cells in excel – concat()”

  1. Hypnos says:

    You are a god in Excel!

    I bow down to you o master of Excel…

  2. Hypnos says:

    But… how do I use it?

    The formula does not appear when i type in ‘=conc…’ in a cell.

  3. Chandoo says:

    @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.

  4. Hypnos says:

    Duh!

    It didn’t work, that is why I wrote… give me some credit dude :) I used to be in your ITCOM.

  5. Chandoo says:

    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…

  6. captsri says:

    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

  7. Chandoo says:

    @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… :)

  8. [...] Concatenate a bunch of cells using simple formula, Generate tag clouds in excel using vba, Master your IFs and BUTs Tags: Analytics, count, excel, [...]

  9. colebro says:

    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?

  10. Chandoo says:

    @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….

  11. Chandoo says:

    @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…

  12. colebro says:

    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?

  13. SheilaC says:

    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.

    :) Bring on the coffee dude…this was HELPFUL. So simple, but practical. Now WTH doesn’t Excel just make this standard code???????????

    LOL – might be too practical for us, right???

  14. Chandoo says:

    @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…

  15. Chandoo says:

    @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 :D


    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…

  16. Chandoo says:

    @SheilaC: you may want to replace the dirty quotes in the code with proper double quotes…

  17. SheilaC says:

    …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

  18. MikeP says:

    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

  19. Chandoo says:

    @SheilaC : Hope you are alright :P, I am happy this helped you.

    @MikeP : thanks for pointing it out, I have changed the code to include your suggestion. :)

  20. Navneet says:

    it was a great help. thanks!!

  21. [...] on text processing using excel: Concat() UDF for adding several cells, Initials from names using excel formulas Categories : Excel Tips | ideas Tagged with: [...]

  22. EStout says:

    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?

  23. Chandoo says:

    @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.

  24. EStout says:

    Thank you so much!!! It worked.

  25. [...] tip: If you are using formulas to create content in a cell by combining various text values and you want to introduce line breaks at certain points … For eg. you are creating an address [...]

  26. [...] the Concat VBA Function I have written can be used to concatenate a range of cells (along with a custom delimiter), it [...]

  27. morgan says:

    you have no idea how much time this saved me!!!! thank you so much! i merged 1395 cells into one!

  28. cybpsych says:

    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?

  29. cybpsych says:

    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″

  30. cybpsych says:

    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″

  31. Chandoo says:

    @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

  32. Daniel Strand says:

    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

  33. Chandoo says:

    @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.

  34. SheilaC says:

    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????

  35. Chandoo says:

    @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…

  36. SheilaC says:

    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?

    • Chandoo says:

      @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.

  37. jUGAD says:

    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 ;)

  38. Chandoo says:

    @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…

  39. jUGAD says:

    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 ;)

  40. David says:

    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!

  41. Chandoo says:

    @David.. you are welcome :)

  42. PJ says:

    This is great!! Have you figured out how to comma or semicolon-delimit the resulting string? That would also be really helpful!

  43. Chandoo says:

    @PJ: The function has an option delimiter parameter, you can pass “,” or “;” to it and it will delimit the values with that.

  44. PJ says:

    Thanks, that worked great! You just saved me a ton of time!

  45. Ed says:

    Many, many thanks. Saved me a huge effort too!

  46. Wade says:

    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

  47. 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)

  48. Chandoo says:

    @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..

  49. Milco says:

    Thanks a lot for your formula. It flawlessly concatenated a range of 100 fields yesterday!

    - Milco

  50. i says:

    @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

  51. i says:

    Sorry about the formatting of my last post. I didn’t realize whitespace wouldn’t be preserved.

  52. Greg says:

    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 :D

  53. Jive says:

    I added another IF/THEN/ELSE statement to avoid placing the last deliminator

    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
    If retVal “” Then
    retVal = retVal + dlm + CStr(cell.Value)
    Else
    retVal = CStr(cell.Value)
    End If
    End If
    Next

    If dlm = “” Then
    retVal = Left(retVal, Len(retVal) – Len(dlm))
    End If

    concat = retVal

    End Function

  54. Jive says:

    and here’s a modification that will concat only if they contain specified content

    Function concatonly(useThis As Range, contains As String, location As Integer, Optional delim As String) As String
    ‘ this function will concatenate a range of cells if they contain a search string and return one string with optional deliminator
    ‘ useful when you have a rather large range of cells that you need to add up
    ‘ format is concatselect(range, search string, search type (0 includes, 1 begins with, 2 end with), Optional deliminator)

    Dim retVal, dlm As String
    Dim stringfound As Boolean
    Dim searchstring As String

    If location > 0 Then
    If location > 1 Then
    searchstring = “*” + contains
    Else
    searchstring = contains + “*”
    End If
    Else
    searchstring = “*” + contains + “*”
    End If

    retVal = “”
    If delim = Null Then
    dlm = “”
    Else
    dlm = delim
    End If

    For Each cell In useThis
    stringfound = cell.Value Like searchstring
    If stringfound = True Then
    If CStr(cell.Value) “” And CStr(cell.Value) ” ” Then
    If retVal “” Then
    retVal = retVal + dlm + CStr(cell.Value)
    Else
    retVal = CStr(cell.Value)
    End If
    End If
    End If
    Next

    If dlm = “” Then
    retVal = Left(retVal, Len(retVal) – Len(dlm))
    End If

    concatonly = retVal

    End Function

    ‘ By the way: Thanks for posting this code – it’s gotta be the best add-in I’ve seen

  55. Jennifer says:

    This worked perfectly…what a time saver! Thank you so much!

  56. brad says:

    hey mate, thanks for code…. BUT I’m trying to concatenate numbers
    eg:
    250
    260
    261
    262
    402
    448
    When i use your code it gives me the result: 250251252253254255256257258259260261262263264402448
    I don’t want the 251 – 259
    I should be seeing:
    250260261262402448
    Please help… I’ve got about 200 strings to concatenate by tomorrow each of about 120 characters!!
    Thanks :)

  57. Hui... says:

    Brad
    Try the following code which must be put in a Code Module
    To use =Concat(A1:A10)
    .
    .
    Function Concat(useThis As Range) As String
    Dim c As Range
    Dim retVal, dlm As String
    retVal = “”
    For Each c In useThis
    retVal = retVal + Trim(CStr(c.Value))
    Next
    concat = retVal
    End Function

  58. Rick Rothstein (MVP - Excel) says:

    Here is a function that I have posted in the past (and which Debra Dalgleish is hosting on her site) that others may find useful (you can intermingle cells, ranges and text constants along with an optional delimiter). See this link…

    http://www.contextures.com/rickrothsteinexcelvbatext.html#combine

  59. brad says:

    AWESOME!!!! thanks for quick response!!! exactly what i needed

  60. flex says:

    hiho!! really nice code ;D exactly what i needed too
    but i’m trying to aggregate something with that function and i can’t get it work… i you know a way, I wold be glad

    is to add an boolean, if its true it count the number of rows. if it’s TRUE it count cells with values and if is more than 1 in the last it remove comma before the value and put ” and ” before the value. like:

    January – March – October – … – December
    with the actual function: January, March, October, December
    what i’m looking:January, March, October and December

  61. Hui... says:

    @Flex
    like
    =Concat(A1:A10,” – “) or =Concat(A1:A10,B1)
    Just change the code to the following
    .
    Function Concat(useThis As Range, Optional sep As String) As String
    Dim c As Range
    Dim retVal, dlm As String
    retVal = “”
    For Each c In useThis
    retVal = retVal + Trim(CStr(c.Value)) + sep
    Next
    Concat = Left(retVal, Len(retVal) – Len(sep))
    End Function

  62. Hui... says:

    @Flex
    Didn’t read your full question
    The following should answer all your queries
    use as
    .
    =Concat(A1:A10)
    JanFebMarApr…Dec
    .
    =Concat(A1:A10,”, “)
    Jan, Feb, Mar, Apr…, Dec
    .
    =Concat(A1:A10,”, “,1)
    Jan, Feb, Mar, Apr…Nov and Dec
    .
    ===
    Function Concat(useThis As Range, Optional sep As String, Optional last As Integer) As String

    Dim c As Range
    Dim retVal, dlm As String
    Dim NE As Integer, NC As Integer
    Dim i As Integer

    retVal = “”

    NR = useThis.Rows.Count
    NC = useThis.Columns.Count
    noitems = Application.WorksheetFunction.Max(NR, NC)

    If last = 1 Then noitems = noitems – 1
    For i = 1 To noitems
    retVal = retVal + Trim(CStr(useThis(i))) + sep
    Next
    If last = 1 Then retVal = Left(retVal, Len(retVal) – Len(sep)) + ” and ” + useThis(noitems + 1) + sep

    Concat = Left(retVal, Len(retVal) – Len(sep))
    End Function

  63. flex says:

    answered really fast ;o

    oh, i was even close..! but MANY. THANKS the result was exact what was expecting to be.
    thanks for the code, will be really useful.

  64. flex says:

    hmm, here again!

    i found something.. when the last cell in the range is blank (“”) the function don’t put the ” and “.
    And if the last cell in the range it’s a number, it returns #error.

    i have to stop here for today, but i changed the code using lines of the function on the top of the page, to ignore the blank cell in the range(not working properly if blank cell is the last).

    here is:
    Function Concat(UseThis As range, Optional sep As String, Optional last As Integer) As String

    Dim c As range
    Dim retVal, dlm As String
    Dim NE As Integer, NC As Integer
    Dim i As Integer

    retVal = “”

    NR = UseThis.Rows.Count
    NC = UseThis.Columns.Count
    noitems = Application.WorksheetFunction.Max(NR, NC)

    If last = 1 Then noitems = noitems – 1
    For i = 1 To noitems
    If CStr(UseThis(i).Value) “” And CStr(UseThis(i).Value) ” ” Then
    retVal = retVal + Trim(CStr(UseThis(i))) + sep
    End If
    Next
    If last = 1 Then
    If CStr(UseThis(i).Value) “” And CStr(UseThis(i).Value) ” ” Then
    retVal = Left(retVal, Len(retVal) – Len(sep)) + ” and ” + UseThis(noitems + 1) + sep
    End If
    End If
    Concat = Left(retVal, Len(retVal) – Len(sep))
    End Function
    ”””””””””””””’

  65. Hui... says:

    @Flex
    People always find a case you don’t test for
    Try this
    ===
    Function Concat(useThis As Range, Optional sep As String, Optional last As Integer) As String

    Dim c As Range
    Dim retVal, dlm As String
    Dim NE As Integer, NC As Integer
    Dim i As Integer
    Dim noItems As Integer

    retVal = “”

    NR = useThis.Rows.Count
    NC = useThis.Columns.Count
    noItems = Application.WorksheetFunction.Max(NR, NC)

    If last = 1 Then noItems = noItems – 1

    For i = 1 To noItems
    retVal = retVal + Trim(CStr(useThis(i))) + sep
    Next
    If last = 1 Then retVal = Left(retVal, Len(retVal) – Len(sep)) + ” and ” + Format(useThis(noItems + 1), “General Number”) + sep
    If last = 1 And useThis(noItems + 1) = “” Then retVal = Left(retVal, Len(retVal) – Len(sep) – 2)

    Concat = Left(retVal, Len(retVal) – Len(sep))
    End Function

  66. Mike says:

    Hi,
    I have tried various options offered, but have not seen a fit for my challenge.

    I have a large range of values in a column. ie B1=”Hello” B2=”There” b3= “How?” etc. I would like to concat them based on a value in another column. For example A1=1, B1=1, c1=1. Basically I want to use a formula to define my range as I have additional values I want to concat seperatly in lower cell ranges. ie b4=”good” b5=”Bye” A4=2, A5=2. Any suggestions?
    Thanks a million!

  67. Hui... says:

    @Mike
    If I understand you correct you want to have a Concat If function
    That is concatenate values if other values meet a criteria
    .
    I have written a small UDF below which will do just that
    .
    Use
    =Concatif(Concat Range, Validation Range, Validation, [Seperator])
    Concat Range is a range of Values/text you want to concatenate together
    Validation Range is a range of Values/Text you want to comapre to a Validation value
    Validation is a Text or Number you want to compare the Validation range against
    Seperator is an Optional seperator and is a Null if not supplied
    .
    eg:
    =concatif(B1:B5,A1:A5,1)
    Will concatenate the Values in B1:B5 where A1:A5 = 1, with no seperator
    =concatif(B1:B5,A1:A5,”Tom”,”-”)
    Will concatenate the Values in Columns B1:B5 where A1:A5 = “Tom”, with a – seperator
    =concatif(C12:G12,C14:G14,”John”,”-”)
    Will concatenate the Values in Rows B1:B5 where A1:A5 = “John”, with a – seperator
    =concatif(C12:G12,A1:A5,D1,”-”)
    Will concatenate the Values in Rows B1:B5 where Column A1:A5 = Cell D1, with a – seperator

    Function ConcatIf(Src As Range, ChkRng As Range, myVal As Variant, Optional Sep As String) As String
    Dim c As Range
    Dim retVal As String
    Dim i As Integer

    retVal = “”
    i = 1

    For Each c In ChkRng
    If c = myVal Then
    retVal = retVal + Src(i) + Sep

    End If
    i = i + 1
    Next

    ConcatIf = Left(retVal, Len(retVal) – Len(Sep))
    End Function

    .
    You may need to check the ” and – characters in VBA

  68. Mike says:

    Hui,
    I greatly appreciate the help. You have no idea!
    I tried the function, your 4th example is the one I am attempting to use.
    Unfortunatly I am getting a compile Syntax error. On what looks like the last line :ConcatIf = Left(retVal, Len(retVal) – Len(Sep))

    I tried it on a simple mock up
    in cell D1= concatif(B1:B4,A1:a4,C1,”-”)

    Column A Column B column C
    123 X 123
    123 Y
    456 Z
    456 Q

    Any other thoughts

  69. Hui... says:

    Yes
    Your formula is ok
    Note my very last line after the code
    .
    Retype the – sign on the line
    ConcatIf = Left(retVal, Len(retVal) – Len(Sep))
    even though it looks like a – it probably isn’t
    .
    In VBA if a line is highlighted Red there is something wrong with the syntax

  70. Mike says:

    Awesome! Awesome! Awesome! I bow down to you Sir. Thank you.

  71. Alex says:

    Thank you so much! I kept getting a #VALUE! error, when there was too much text (only like 200 characters… excel 2010), when I would delete certain parts, like parentheses out of the text it would work, however due to the nature of the text, I needed them. Anyways, this code did the trick, also I got the range code added it, sure saved a long list of cells!!!

  72. Wouter says:

    This works perfectly for letters, but when I try to use numbers, I get a #VALUE! error. Any ideas? Using excel 2010.

  73. Hui... says:

    @Wouter
    Try the following which has been slightly modified

    Function ConcatIf(Src As Range, ChkRng As Range, myVal As Variant, Optional Sep As String) As String
    Dim c As Range
    Dim retVal As String
    Dim i As Integer
    retVal = ""
    i = 1
    For Each c In ChkRng
    If c = myVal Then
    If WorksheetFunction.IsNumber(Src(i)) Then
    retVal = retVal + Trim(Str(Src(i))) + Sep
    Else
    retVal = retVal + Src(i) + Sep
    End If
    End If
    i = i + 1
    Next
    ConcatIf = Left(retVal, Len(retVal) - Len(Sep))
    End Function

  74. Pablo says:

    Wonderful code. I have just started using excel 2010 and use the code to create a string that the “get external data – msn stock quote function” works from. With msn now no longer supporting the stock quote addin (excel 2003) I was using, I found this code a god send. I have large list of stocks and this code has saved me from doing a massive concatenate formula. Brilliant, love it, thank you.

  75. Andrea says:

    More than 3 years after the original post and this is still helping people! I took the version you (Chandoo) wrote for Sheila and then added the bit that Jive contributed to leave out the last delimiter (at the end) and it is absolutely perfect and saved me from what would definitely have been a major migraine. THANK YOU!!! FYI, I used this in MS Excel 2010.

  76. Mihajlo says:

    This is a great function and I’m impressed with the discussion too.
    I did not find in the comments way to concatenate numeric cells as strings.
    I have custom format in Excel (“Text”0000, which gives me numbering Text0001, Text0002, etc) that actually stores numbers in cells. The concat UDF returned 1, 2, 3, … as a result instead of Text0001, Text0002, Text0003, …
    Well, I googled another solution, instead of using cell.value I used cell.text, it worked.
    So, these lines
    [code]For Each cell In useThis
    if cstr(cell.value)"" and cstr(cell.value)" " then
    retVal = retVal & cstr(cell.Value) & dlm
    end if [/code]
    I changed to these
    [code]For Each cell In useThis
    if cstr(cell.text)"" and cstr(cell.text)" " then
    retVal = retVal & cstr(cell.text) & dlm
    end if [/code]
    Cheers

  77. Hui... says:

    Mihajlo
    Well done & Correct
    As you’ve discovered
    cell.Value returns the Value
    cell.Text returns the displayed Text
    This is great where you want date strings

  78. floydbloke says:

    Thanks for this.

    I had a need to concatenate a range, quickly discovered the limitations of the built-in function, quick Google search, found your solution, pasted the code into the VB editor and voila.

    You’ve saved me hours of head scratching and frustrations.

  79. Chris says:

    Thanks! This worked for me and it was exactly what I needed to use in Excel 2010.

  80. Rick says:

    Thank you! You’re doing the lord’s work!

  81. Joe says:

    Hey there! This is a great function and has proved to be very useful, but I’ve run into what seems to be the same problem that Alex posted on May 2nd. I’m using concat() to dynamically concatenate a range of cells (because I didn’t know about the ConcatIf() function before finding it in these comments, but the work is basically done and I don’t feel like changing it if I don’t have to :P) and once I get over 100 cells that meet my criteria I get a #VALUE error. I need it to concatenate up to ~180 cells at most, so at the moment I can’t use this function almost half of the time. HALP! Thanks again!

  82. Hui... says:

    @Joe
    .
    There should be no limit (until you run out of memory) to the number of cells you can Contaif
    .
    I am able to easily Concatif 500 cells of 10 characters together using the Code from Post No. 70.
    =Concatif(A1:A500:B1:B500,1)
    .
    Can you be more specific about what your doing or send me your file?

  83. ScottieO says:

    This is awesome. Thank you so much for creating it. You saved me soooooo much time.

  84. Kevin says:

    This was so IMMENSELY useful. Thank you so very much for posting this Chandoo!!!

  85. Steve Warner says:

    When using this add in, I’m trying to use it on at least 15 cells, but it gives me a value error. If I reduce it to 5 cells, it will work. Any thoughts?

  86. Dave says:

    Thanks for the CONCAT() plug in, very helpful! I’ve concatenated a HUGE range (over 400 cells) and the result is almost 12,000 characters. Excel tends to truncate the _display_ of all characters however. I’m convinced the formula works as advertised because if I copy the cell into Word or Notepad, all the characters are there. This is not a complaint! :) Just an fyi for anyone who might experience the same behavior.
    Thanks. Dave

  87. Ryan says:

    Thank you guys for such a great code, I found the concatif very helpful, but would you please help me with my case?

    I have the following list to concate

    #   Code     Value
    1   22         1001
    2   22         1002
        (Blank)  (Blank)
    3   22         1003
    4   22         1004
    5   22         1005
        (Blank)  (Blank)
    7   22         1006
    .
    .
    so on

    How can I concate Value with Code=22 and start from #3 to the last of the list?
    ConcatIf(Value, Code, 22, “, “, StartFrom (#3)) 

  88. Niegel says:

    Wonderful, just what I needed! Thanks for sharing!

  89. OdgeUK says:

    This really helped! Thanks! I had a column of data (numbers wrapped in quotes and suffixed with a comma) and this enabled me to place them all in one cell, one one line so I could then export this list into a WQL/SQL query. Saved a lot of time. Thanks again.

  90. Just found this great function from a google search. It Rocks!! It works easily and will especially help me when I do concatenated text data sends back into our FP&A system.
    I also love that it can give me just the results of cells that are full when I check very long ranges with blanks in it. Plus it is fast too.
    Thanks so much. You Rock!! This site has given me so much over the years. Keep up the great work.

  91. [...] Maybe: How to add a range of cells in excel – concat() | Chandoo.org – Learn Microsoft Excel Online [...]

  92. sushant harit says:

    I want to do this without VBA, is it possible ?? when i use the concatenate() func and input a cell range in a column it give a #Value error. however when i manually input cells seperated my comma it is working fine. help me out please.

    Thanks
    Sushant

  93. Jim2k says:

    had I read down the comments first I would have seen that @Hui had already written a concatif() function, however I did not do that before creating my own using the original concat() as inspiration, so here’s my own version I hope you find useful

    Public Function CONCATIF(criteria As Variant, criteria_range As range, Optional concat_range As range, Optional delim As String) As String
    ‘this function will concatenate a range of cells that meet the specified criteria and return one string
    ‘credit to chandoo.org for the original concat() function extended here to accept criteria evaluaition
    ‘How to add a range of cells in excel – concat() – http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

    Dim retVal, dlm As String
    Dim i As Integer

    retVal = “”
    If delim = Null Then
    dlm = “”
    Else
    dlm = delim
    End If

    If concat_range Is Nothing Then
    Set concat_range = criteria_range
    End If

    i = 1

    For Each cell In concat_range
    If criteria_range.Rows(i).Value = criteria Then
    If CStr(cell.Value) “” And CStr(cell.Value) ” ” Then
    retVal = retVal & CStr(cell.Value) & dlm
    End If
    End If
    i = i + 1
    Next

    If dlm “” Then
    retVal = Left(retVal, Len(retVal) – Len(dlm))
    End If

    CONCATIF = retVal

    End Function

    Regards,

    Jim

  94. Sunny says:

    Name Coins
    Ken Douglas 500
    Ken Douglas 400
    Maria Jones 111
    Warren Mayfield 245
    Maria Jones 344

    Hi,

    please look into the above data, I need a favor if anyone plz let me know how should I look coins value in an other column name wise. If I use vlookup It shows me only first value (500) for Ken Douglas.

  95. Litty says:

    hi,

    can you please help me to concatenate 2 strings in which one string is italics and i want the same format after concatenation. Is this possible?

  96. Denis says:

    If you add Chr(10) you can keep page breaks (alt+enter). So the text keep formatting.

    Sub FormMergeCells()
    Dim result As String

    For Each cell In Selection.Cells
    If Not cell.Value = vbNullString Then
    result = result & Chr(10) & Trim(cell.Value) & ””
    End If
    Next

    Application.CutCopyMode = False
    With Selection
    .Clear
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlTop
    .WrapText = True
    .MergeCells = True
    End With

    Selection.Cells(1, 1).Value = result
    End Sub

  97. jas says:

    thank u !!

  98. Bob Arnett says:

    Concat is great. I was wondering, however, how one could suppress extra separators when a cell is empty. I keep ending up with results like: “Jim,,, Sally, Debbie, Carl,,,,,,” when there are empty cells within the range.

    • Hui... says:

      @Bob
      Chandoo’s original code does exactly that, suppresses the blank cells

      Which version from above are you using?

      Hui…

      • Bob Arnett says:

        I used the link to the installer “xla” file. I don’t know which version that is. I could copy the original code but I didn’t understand where to put it.

        • Hui... says:

          @Bob

          I think Chandoo updated the code just below the Addin but didn’t upgrade the addin.

          I have now updated the addin and so the functionality should be as you require.

          Hui…

  99. Bob Arnett says:

    Thanks worked great.

  100. jo says:

    Thanks! you’ve just saved me so much aggro.
    Note to self – time to pick up some vba skills.

Leave a Reply