Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: VBA: Join function help

  1. #1
    Board Regular
    Join Date
    Jun 2008
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    21,243
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Join function help

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

  3. #3
    Board Regular
    Join Date
    Jun 2008
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    21,243
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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
    8,390
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    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
    8,390
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA: Join function help

    No, Mike had a different approach. Sorry.

  7. #7
    Board Regular
    Join Date
    Jun 2008
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Join function help

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

User Tag List

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