Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    concatenete cells in one... (2000 sr-1)

    is possible with a little macro to concatenate in D1 (in text format) the value in column A?
    I have insert in D1 the example....
    Tks.

  2. #2
    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: concatenete cells in one... (2000 sr-1)

    You could add this function to a module
    <pre>Option Explicit
    Function MyConc(rng As Range) As String
    Dim rCell As Range
    MyConc = ""
    For Each rCell In rng
    MyConc = MyConc & " " & rCell.Value
    Next
    End Function</pre>


    Then in D1 enter:
    <pre>=myConc(A2:A45)</pre>


    Steve

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: concatenete cells in one... (2000 sr-1)

    Hi Steve... but is possible to call a function thru a formula in cell? THIS IS VERY GOOD FOR ME!!!!!!!!!
    I have know now a new other way....!!!
    Tks many tks....

    Ahhhhh.... the function work fine.

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: concatenete cells in one... (2000 sr-1)

    I would suggest an addition to Steve's custom function: run the concatenated string through the TRIM() worksheet function to remove leading and trailing spaces.

    Function MyConc(rng As Range) As String
    Dim rCell As Range
    MyConc = ""
    For Each rCell In rng
    MyConc = MyConc & " " & rCell.Value
    Next
    MyConc = Application.Trim(MyConc)
    End Function

    Otherwise, the result will always begin with a space. Btw, I have a similar custom function that I use quite often, which allows the delimiter to be any string ("_", "---","", " then ", etc). I modified names to conform to Steve's example above.

    Function MyConc2(cDelim As String, rng As Range) As String
    Dim rCell As Range, FirstTimeThrough As Boolean
    MyConc2 = ""
    FirstTimeThrough = True
    For Each rCell In rng
    If FirstTimeThrough Then
    MyConc2 = rCell.Value
    FirstTimeThrough = False
    Else
    MyConc2 = MyConc2 & cDelim & rCell.Value
    End If
    Next
    MyConc2 = Application.Trim(MyConc2)
    End Function

    For example, if cells D210 contain email addresses, one could build a To: list very easily with the function, like

    =MyConc2(";",D210)

    So, how does one get the text to appear in Courier? Or is there another monospace font in the 1-Click TagPanel that I'm missing?

    Aloha,
    JohnJ

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

    Re: concatenete cells in one... (2000 sr-1)

    You can use <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags. This will not only set a monospaced font, but also preserve indentation and mutiple spaces. Its disadvantage is that you can't copy and paste code directly to the Visual Basic Editor - it is pasted as one long line. As a workaround, you can paste into a worksheet or into a Word document, then copy again and paste into a module.

    Alternatively, you can use <!t>
    Code:
    <!/t> and <!t>
    <!/t> tags. This sets a monospaced font, but doesn't preserve indentation and multiple spaces.

    Example (using <!t>
    Code:
    <!/t> and <!t>
    <!/t> tags in combination with <!t>[tab]<!/t> tags):
    <code>
    Function MyConc2(cDelim As String, rng As Range) As String
    Dim rCell As Range
    For Each rCell In rng
    MyConc2 = MyConc2 & cDelim & rCell.Value
    Next rCell
    If Not MyConc2 = "" Then
    MyConc2 = Trim(Mid(MyConc2, Len(cDelim) + 1))
    End If
    End Function
    </code>
    I modified your code slightly to avoid having a test inside the loop.

  6. #6
    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: concatenete cells in one... (2000 sr-1)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> Nice additions

    Steve

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: concatenete cells in one... (2000 sr-1)

    Elegant and concise. Thanks, Hans.

    (added shortly after) A comment: For me, the worksheet function TRIM() has an advantage over the VB function Trim in that it removes all spaces except for single spaces between words. In my work, I am often concatenating text values that have been input by the technologically-challenged, and extra spaces are common. Heck, if you can't see em, they ain't there, right?

    Aloha,
    JohnJ

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

    Re: concatenete cells in one... (2000 sr-1)

    OK, that's a good reason for using Application.Trim.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: concatenete cells in one... (2000 sr-1)

    Big explain and good code...
    Tks Hans

Posting Permissions

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