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

    Put comma in function (2003)

    I have the following function that concatenates and puts a space between. I would like a comma separator.

    Public Function MultiCat2(ByRef rRng As Excel.Range, _
    Optional ByVal sDelimiter As String = "") As String
    Dim rCell As Range
    For Each rCell In rRng
    MultiCat2 = MultiCat2 & sDelimiter & " " & rCell.text
    Next rCell
    MultiCat2 = Trim(Right(MultiCat2, Len(MultiCat2) - Len(sDelimiter)))
    End Function

    How would I change this from a space delimiter to a comma delimiter?

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Put comma in function (2003)

    Hi Linda,

    I'm taking a chance by saying, try this...

    Replace
    MultiCat2 = MultiCat2 & sDelimiter & " " & rCell.text
    With
    MultiCat2 = MultiCat2 & sDelimiter & "," & rCell.text
    Regards,
    Rudi

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

    Re: Put comma in function (2003)

    Or simply Chr(34) twice - this is the "straight" double quote character.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Put comma in function (2003)

    I did figure that out but now I want to comma delimit plus put quotes around the numbers once they are concatenated with comma between. For example: "123456","123455","123456". Is this possible in that function?

    I figured it out. Thanks for your help.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Put comma in function (2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> If you get rid of the declared variable Delimiter you should also delete it from the line:
    MultiCat2 = MultiCat2 & sDelimiter & Chr(147) & "," & Chr(147) & rCell.Text

    and change it to:
    MultiCat2 = MultiCat2 & Chr(147) & "," & Chr(147) & rCell.Text

    otherwise people will get an error if they force declaration of variables with "Option Explicit"

    On a side note: The "delimiter option" (as in keeping it) to me would be a do this automatically by calling the function and adding the desired delimter as the last parameter. One has the option as general with the default delimiter (if none is set) and then change it explicitly by adding it as the last option. I think that was the intent of the optional parameter.

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Put comma in function (2003)

    Thanks Steve, I overlooked that, I shall change it in my code to make it clearer
    Jerry

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Put comma in function (2003)

    <P ID="edit" class=small>(Edited by Jezza on 11-Jan-08 21:10. On the advice of Steve, I removed a rogue & sDelimiter, I had overlooked when coding.)</P>Hi Linda

    How about this without the option of a delimiter

    <code>
    Public Function MultiCat2(ByRef rRng As Excel.Range) As String
    Dim rCell As Range
    For Each rCell In rRng
    MultiCat2 = MultiCat2 & Chr(147) & "," & Chr(147) & rCell.Text
    Next rCell
    MultiCat2 = Trim(Right(MultiCat2, Len(MultiCat2) - 2)) & Chr(147)
    End Function
    </code>
    Jerry

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Put comma in function (2003)

    <P ID="edit" class=small>(Edited by Rudi on 11-Jan-08 23:24. I guess I have to adjust mine too Just to keep things accurate!)</P>You have two opening quotes. How about MultiCat2 = MultiCat2 & Chr(147) & "," & Chr(14<font color=blue>8</font color=blue>) & rCell.Text
    Regards,
    Rudi

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Put comma in function (2003)

    Personally I would (as alluded to in another post) use the optional delimiter parameter as intended and use this modified function:

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


    You can combine A1:A10 with the formula:
    =multicat2(A1:A3)

    Which will combine them with no delimiter at all. This will add spaces:
    =multicat2(A1:A3, " ")
    This commas:
    =multicat2(A1:A3, ",")

    And this will put the quotes and commas around it:
    =CHAR(34)&multicat2(A1:A3, CHAR(34)&", "&CHAR(34))&CHAR(34)

    All without changing the function. If you want the default delimiter (when no delimiter is explicitly used) to be something other than a null that can be changed in the first line of the function. For example to make it a comma-space:

    Public Function MultiCat2(ByRef rRng As Excel.Range, _
    Optional ByVal sDelimiter As String = ", ") As String

    But no matter what the default delimiter, the function allows a different one with the explicit listing when you call it.

    I am not sure why the line:
    MultiCat2 = MultiCat2 & sDelimiter & " " & rCell.Text

    Is even in the code since it bypasses the delimiter parameter and always puts a space within the combined text. I think it works better to use a more general one and explicitly add the space if desired.

    Steve

Posting Permissions

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