Results 1 to 9 of 9

VBA: Join function help

This is a discussion on VBA: Join function help within the Excel Questions forums, part of the Question Forums category; I have little problem with the join function Join(soucearray, delimiter) Let's say I have an array of names, but some ...

  1. #1
    Board Regular
    Join Date
    Jun 2008
    Posts
    164

    Default VBA: Join function help

    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!

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,272

    Default Re: VBA: Join function help

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

  3. #3
    Board Regular
    Join Date
    Jun 2008
    Posts
    164

    Default Re: VBA: Join function help

    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.

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,272

    Default Re: VBA: Join function help

    Unlike the code above, this has only one space between the first and second double quotes
    Code:
    Replace(WorksheetFunction.Trim(Join(myNames)), " ", ",")

  5. #5
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,972

    Default Re: VBA: Join function help

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

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

  6. #6
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,972

    Default Re: VBA: Join function help

    No, Mike had a different approach. Sorry.

  7. #7
    Board Regular
    Join Date
    Jun 2008
    Posts
    164

    Default Re: VBA: Join function help

    Thanks guys. But it still does not work right.

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

  8. #8
    Board Regular
    Join Date
    Jun 2008
    Posts
    164

    Default Re: VBA: Join function help

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

  9. #9
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: VBA: Join function help

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com