Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate Function (2000)

    I have the following function:
    Public Function MultiCat(ByRef rRng As Excel.Range, _
    Optional ByVal sDelimiter As String = "") As String
    Dim rCell As Range
    For Each rCell In rRng
    MultiCat = MultiCat & sDelimiter & rCell.Text
    Next rCell
    MultiCat = Mid(MultiCat, 1)
    End Function

    My question is - Currently if I have 23 in Col A and 45 in Column B and 3445 in Column C
    By putting =Personal.xls!MultiCat(A1:C1) in Col D I get 23453445 in column D.
    My question is - is there any way to adjust the code so the result would be 23 (space) 45 (space) 3445. Is there a way of putting dashes between the numbers as another alternative? Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenate Function (2000)

    Specify the character you want between the values as second argument in the function (as a quoted string):
    If you want spaces:
    <code>=Personal.xls!MultiCat(A1:C1," ")</code>
    If you want dashes:
    <code>=Personal.xls!MultiCat(A1:C1,"-")</code>

    Note: you should change
    <code>MultiCat = Mid(MultiCat, 1)</code>
    to
    <code>MultiCat = Mid(MultiCat, Len(sDelimiter) + 1)</code>
    to get rid of the first delimiter.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate Function (2000)

    Does this do what you want?

    <pre>Public Function MultiCat(ByRef rRng As Excel.Range, _
    Optional ByVal sDelimiter As String = "") As String
    Dim rCell As Range
    For Each rCell In rRng
    MultiCat = MultiCat & sDelimiter & " " & rCell.Text
    Next rCell
    MultiCat = Trim(Right(MultiCat, Len(MultiCat) - Len(sDelimiter)))
    End Function
    </pre>

    Legare Coleman

Posting Permissions

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