+ Reply to Thread
Results 1 to 34 of 34

ConcatAll UDF by TigerAvatar

  1. #1
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    ConcatAll UDF by TigerAvatar

    Hello all,

    I know there are various versions of this same thing out there (AConcat by Harlan Grove, MCONCAT from the MoreFunc addin, etc), but I wanted to throw my version in the ring (also so I can find it/point people to it if it ever comes up). I developed it to work with ranges, arrays, and collections so that it can be used in worksheet formulas (both regular and array formulas) as well as with other VBA code that might be using collections. It ignores anything passed to it that has a Len(0) (which I have found very handy).

    The function call:
    ConcatAll(varData, [sDelimiter])

    The function takes two arguments:
    varData: This is a Required variant that can be a Range object, Array, Collection, or single item (like a String or Double value).
    sDelimiter: This is an Optional string used to determine how the data is concatenated. The default is vbNullString, so there will be no separation if this argument is omitted.

    Examples:
    =ConcatAll({"a","b","c"}) -> will result in "abc"
    =ConcatAll({"a","b","c"},"/") -> will result in "a/b/c"

    If "a" "b" "c" are in cells A1:A3, then you could similarly use:
    =ConcatAll(A1:A3) -> will result in "abc"
    =ConcatAll(A1:A3,", ") -> will result in "a, b, c"

    Here is the code:
    Please Login or Register  to view this content.


    As this is in Tips and Tutorials, here are some quick steps to implenting the code in a workbook:
    How to use a User Defined Function (UDF):
    1. Make a copy of the workbook the macro will be run on
      • Always run new code on a workbook copy, just in case the code doesn't run smoothly
      • This is especially true of any code that deletes anything
    2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
    3. Insert | Module
    4. Copy the provided code and paste into the module
    5. Close the Visual Basic Editor


    Now you will have the UDF available and can use it as a worksheet formula as shown in the examples above. It can also be called from within VBA for joining collections, multiple dimensional arrays, etc. It is my sincere hope that others will find this useful.

    Regards,
    ~TigerAvatar
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  2. #2
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: ConcatAll UDF by TigerAvatar

    Thank you for the super UDF!

  3. #3
    Registered User
    Join Date
    11-15-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: ConcatAll UDF by TigerAvatar

    Thank you this is very nice, but is it possible to remove duplicates in the row.

    To simplify matters, in A1:E1 39 39 34 34 101

    In G10 I will use =ConcatAll(A1:E1,", ") but the result is 39, 39, 34, 34, 101

    Can the outcome instead be 39, 34, 101?

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: ConcatAll UDF by TigerAvatar

    LittleFry,

    Great question! In the updated code below, I added a third optional argument:
    [bUnique]: This is an Optional boolean value (true/false) that is used to determine if the output should be unique results only. The default is False, so there will be duplicate results if this argument is omitted.

    In your case, the formula would then become:
    =ConcatAll(A1:E1,", ",TRUE)

    The result of that is: 39, 34, 101


    Here is the updated code, with comments
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-21-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: ConcatAll UDF by TigerAvatar

    I tried using this code and added it into my Visual Basic. But excel 2007 is still erroring in #NAME? what's going on?

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: ConcatAll UDF by TigerAvatar

    afufoo,

    Attached is an example file containing the ConcatAll UDF. The formula is in cell D2, and you can see the code by pressing Alt+F11 to open the Visual Basic Editor. In there in Module1 is the code for ConcatAll. You may have to enable macros when you open the document in order for the UDF to work properly. That goes for any document that contains VBA code.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-21-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: ConcatAll UDF by TigerAvatar

    works perfectly now! thank you much!!

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: ConcatAll UDF by TigerAvatar

    It has been suggested to me that I can use TypeOf instead of TypeName. This is faster because it doesn't have to perform a string comparison to determine what type of variable is being processed. Here is the updated code using TypeOf, credit for the suggestion goes to shg. Thanks shg!
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    03-13-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: ConcatAll UDF by TigerAvatar

    Doing a quick search, you've done exactly what was needed, Thank You! Had I accepted the money that was offered to get my co-workers what they needed, I would have asked for an address to send 75% of it to.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ConcatAll UDF by TigerAvatar

    I have used both MCONCAT and aconcat. While they both are useful I think they are limited in scope by design.

    I have been looking for a general purpose "concat if" UDF that is easier to use in that all or most of the processing takes place in the UDF rather than having to do a lot of the processing with worksheet functions.

    I'm not much of a programmer so I'm only able to use what I can find on the net!

    So far in some light testing I like what your UDF does. I have a question though regarding this:

    It ignores anything passed to it that has a Len(0)
    Consider this example in the range A2:B5...

    North.....80
    North.....[empty cell]
    South....30
    West.....26

    Then, this array formula:

    =concatall(IF(A2:A5="North",B2:B5,""),", ")

    Returns the string: 80, 0

    Why is that 0 there? Doesn't the empty cell evaluate to LEN(...)=0 ?

    If I concatenate a null string to the target range then it works as expected:

    =concatall(IF(A2:A5="North",B2:B5&"",""),", ")

    Here's the equivalent MCONCAT/aconcat formula:

    =SUBSTITUTE(TRIM(aconcat(IF(A2:A5="North"," "&B2:B5,"")))," ",", ")

    Quite a difference!

    I look forward to your feedback.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  11. #11
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: ConcatAll UDF by TigerAvatar

    The 0 is there because Excel feeds its best guess at interpreted values of B2:B5. Because B3 is blank, Excel feeds the number 0 instead, and the number 0 has a length of 1. This can be overcome with a minor adjustment:
    =concatall(IF(A2:A5="North",B2:B5&"",""),", ")

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ConcatAll UDF by TigerAvatar

    Ok, it must be in the processing of the IF function.

    A1 = empty cell
    A2 = 1
    A3 = empty cell
    A4 = empty cell
    A5 = empty cell

    =concatall(A1:A5,", ")

    Returns 1 (as expected)

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,444

    Re: ConcatAll UDF by TigerAvatar

    Quote Originally Posted by Tony Valko View Post
    Ok, it must be in the processing of the IF function.

    A1 = empty cell
    A2 = 1
    A3 = empty cell
    A4 = empty cell
    A5 = empty cell

    =concatall(A1:A5,", ")

    Returns 1 (as expected)
    For vertical range
    =VConcat(A1:A5,", ",TRUE)

    For horizontal range
    =HConcat(A1:E1,", ",TRUE)
    where True for unique value only.

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    04-17-2013
    Location
    North Carolina
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: ConcatAll UDF by TigerAvatar

    Awesome & simplified. Thank you.

  15. #15
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: ConcatAll UDF by TigerAvatar

    @tigeravatar

    Excellent job. Thanks for sharing.
    Marcelo Branco

  16. #16
    Registered User
    Join Date
    11-09-2012
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: ConcatAll UDF by TigerAvatar

    Hi tigeravatar,

    I am using your ConcatAll UDF and it is working great. I am wondering if the code can be updated to accommodate the following...

    The cells I am concatenating are numbers (ie; 10, 20, 30). I have formatted the cells to display the numbers as 010, 020, 030, etc. Is it possible to retain these leading 0s in the ConcatAll UDF?

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ConcatAll UDF by TigerAvatar

    Are there any empty cells in the range?

    Are all the numbers 3 digits including the leading 0?

  18. #18
    Registered User
    Join Date
    11-09-2012
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: ConcatAll UDF by TigerAvatar

    Yes, there is a possibility of empty cells in the range and yes all the numbers should be 3 digits including the leading 0.

  19. #19
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ConcatAll UDF by TigerAvatar

    Try it like this...

    Data Range
    A
    B
    C
    2
    010
    -----
    010, 020, 030, 040
    3
    4
    020
    5
    030
    6
    7
    040
    8
    9
    10


    The entries in column A are numbers formatted to display as 000.

    This array formula** entered in C2:

    =concatall(IF(A2:A10<>"",0&A2:A10,""),", ")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  20. #20
    Registered User
    Join Date
    11-09-2012
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: ConcatAll UDF by TigerAvatar

    This formula works for two-digit numbers but not for single digit or triple digit numbers. If a number is single digit (like 7) it needs to be displayed as 007. If a number already has three digits, there is no need to add any leading zeros.

  21. #21
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ConcatAll UDF by TigerAvatar

    Try this version...

    Data Range
    A
    B
    C
    2
    001
    -----
    001, 020, 333, 000, 100
    3
    020
    4
    333
    5
    6
    000
    7
    8
    9
    100
    10


    Still array entered:

    =concatall(IF(A2:A10<>"",TEXT(A2:A10,"000"),""),", ")

  22. #22
    Registered User
    Join Date
    11-09-2012
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: ConcatAll UDF by TigerAvatar

    That works great! Thank you.

  23. #23
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ConcatAll UDF by TigerAvatar

    You're welcome. Thanks for the feedback!

  24. #24
    Registered User
    Join Date
    01-21-2014
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: ConcatAll UDF by TigerAvatar

    Hi tigeravatar

    I am using your ContactAll UDF, which works great by the way!, but I was wondering if it is possible to update the code to accomodate the concatenation of different ranges at different positions, meaning that I would like to concatenat range A1:A3 with D5:D7 with J2 and so on.... Is this possible?

    Thanks for a great work!

  25. #25
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: ConcatAll UDF by TigerAvatar

    Hello AndersJS,

    Unfortunately, I think the best I can do without greatly modifying the code would be a formula that looks like this:
    Please Login or Register  to view this content.

  26. #26
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,840

    Re: ConcatAll UDF by TigerAvatar

    I'm not sure how well my effort stacks up against the more experienced guys here but anyway, here it is:
    Please Login or Register  to view this content.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  27. #27
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,312

    Re: ConcatAll UDF by TigerAvatar

    Either using Tiger's code or Jindon's to concatenate, what type of modification is required to check another column for a number?

    So in this example, I've used Jindon's Function from post #13

    =VConcat(H2:H251,", ",TRUE)

    but now I would like to limit the concatenation string to only those in range H2:H251 which also have a 2 in range B2:B251

    Any thoughts?
    HTH
    Regards, Jeff

  28. #28
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,840

    Re: ConcatAll UDF by TigerAvatar

    I have recently found out that I prefer to use an array formula rather than more code to solve problems like that.

    I now use this small piece of code:
    Please Login or Register  to view this content.
    Arrayformula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Jacc; 12-03-2015 at 01:27 AM.

  29. #29
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,312

    Re: ConcatAll UDF by TigerAvatar

    Thank you Jacc. Works great

  30. #30
    Registered User
    Join Date
    09-07-2016
    Location
    Buffalo, NY
    MS-Off Ver
    Office 2013
    Posts
    1

    Re: ConcatAll UDF by TigerAvatar

    I have to say this saved my bacon in a royal way. I do have one question and I hope that it is not a stupid one, but is there a way to save this code and have it available for all new excel documents? Is there some super simple way of storing this so all I have to do is simply create a new excel file and go =ConcatAll and go about my business?

  31. #31
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ConcatAll UDF by TigerAvatar


  32. #32
    Forum Contributor IonutC's Avatar
    Join Date
    01-28-2016
    Location
    Bucharest, Romania
    MS-Off Ver
    Office 2019
    Posts
    486

    Re: ConcatAll UDF by TigerAvatar

    Thanks guys!

    If i would knew this UDF back in the days...oh man....i had many days of unslept night...
    i have a code that should be doing a concatenation like this, but it looks like your UDF is better.
    the code i got from a VBA fellow from this forum, whom I really thank a lot, as he helped me with my issue.
    I post the code here, for others, if they might need this.

    Please Login or Register  to view this content.
    Please consider:
    Be polite. Thank those who have helped you.
    Click the star icon in the lower left part of the contributor's post and add Reputation. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

  33. #33
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,078

    Re: ConcatAll UDF by TigerAvatar

    hi, guys, experts and mod thank you for this good and very useful post
    I was enjoying this site very well,
    I had a question before, now again in my mind after seeing this post
    I want to concatenate with multiple criteria like sumifs does

    is it possible????? then please make a udf with multiple criteria.
    This Udf By @tigeravatar
    is working very well with one criteria
    Please Login or Register  to view this content.
    what about i want add three or more criteria

    btw thank you for udf and hope soon any will help on this

    as
    Use Code-Tags for showing your code :
    Please mark your question Solved if there has been offered a solution that works fine for you
    If You like solutions provided by anyone, feel free to add reputation using STAR *

  34. #34
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,905

    Re: ConcatAll UDF by TigerAvatar

    HaroonSid you have been a member long enough to know not to post questions on other members threads. Please start your own thread and reference this 1 if needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1