VBA: Join function help

[h711]

I have little problem with the join function
Join(soucearray, delimiter)

Let's say I have an array of names, but some of the names are null. So I only need the join function to join those are not null. For example:

Name1=
Name2
Name3="Tom"
Name4
Name5="Jack"

When I use

Join(Array(name1, name2, name3, name4, name5), ",")

I need the result to be: Tom,Jack
not: ,,Tom,,Jack

I don't know which name is null and which may have value in it, so I need to include the full list of the name there.

Thanks for the help!

 

[mikerickson]

Replace(WorksheetFunction.Trim(Join(myNames)), "  ", ",")

 

[h711]

Thanks, but this replace with remove all the ",". I still need the "," between the names, such as Tom,Jack

I only need to remove those extra "," caused by null string.

 

[mikerickson]

Unlike the code above, this has only one space between the first and second double quotes

Replace(WorksheetFunction.Trim(Join(myNames)), " ", ",")

 

[Fazza]

I think Mike just mistyped that. The replace is of two separators with one.

replace$(join$(myNames, ",,", ","))

 

[Fazza]

No, Mike had a different approach. Sorry.

 

[h711]

Thanks guys. But it still does not work right.

Let's say only Name5 has value. After these statements the result is still: ,Jack

 

[h711]

My bad, it works. Thanks!!!!!

 

[jindon]

Join(Split(WorksheetFunction.Trim(Join(Array(name1, name2, name3, name4, name5)))),",")