# Off Topic > Tips and Tutorials >  >  ConcatAll UDF by TigerAvatar

## 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):Make a copy of the workbook the macro will be run onAlways run new code on a workbook copy, just in case the code doesn't run smoothlyThis is especially true of any code that deletes anythingIn the copied workbook, press ALT+F11 to open the Visual Basic EditorInsert | ModuleCopy the provided code and paste into the moduleClose 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

----------


## sans

Thank you for the super UDF!  :Smilie:

----------


## LittleFry

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?

----------


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

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

----------


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

----------


## afufoo

works perfectly now! thank you much!!

----------


## 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.
```

----------


## rwremsik

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.  :Smilie:

----------


## Tony Valko

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!  :Smilie: 

I look forward to your feedback.

----------


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

----------


## Tony Valko

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)

----------


## jindon

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



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

----------


## Laudar

Awesome & simplified.  Thank you.

----------


## mlcb

@tigeravatar

Excellent job. Thanks for sharing.

----------


## msawyer

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?

----------


## Tony Valko

Are there any empty cells in the range?

Are all the numbers 3 digits including the leading 0?

----------


## msawyer

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

----------


## Tony Valko

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.

----------


## msawyer

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.

----------


## Tony Valko

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

----------


## msawyer

That works great! Thank you.

----------


## Tony Valko

You're welcome. Thanks for the feedback!  :Cool:

----------


## AndersJS

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!

----------


## 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.
```

----------


## Jacc

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

----------


## jeffreybrown

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?

----------


## Jacc

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: 

```
Please Login or Register  to view this content.
```

----------


## jeffreybrown

Thank you Jacc.  Works great

----------


## MassConfusion001

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?

----------


## Tony Valko

See this...

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

----------


## IonutC

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

----------


## HaroonSid

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

----------


## FDibbins

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

----------

