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

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.

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.

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.

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.

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&"",""),", ")

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)

@tigeravatar

Excellent job. Thanks for sharing.

Are there any empty cells in the range?

Are all the numbers 3 digits including the leading 0?

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.

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"),""),", ")

You're welcome. Thanks for the feedback!

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.

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.

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?

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:

Please Login or Register  to view this content.

Thank you Jacc. Works great

See this...

https://support.office.com/en-us/art...1-ae005a9bc790

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.

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

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