Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merging multiple cells (2003)

    How can I merge 61 cells into 1 ?
    each cell has an international dialling code which all start with 00, in the merged cell they need to be separated by a space and comma.

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

    Re: Merging multiple cells (2003)

    Put this function in a standard module:

    Function Concat(oRange As Range, Optional strSep As String) As String
    Dim oCell As Range
    For Each oCell In oRange.Cells
    Concat = Concat & strSep & oCell.Value
    Next oCell
    Concat = Mid(Concat, Len(strSep) + 1)
    End Function

    Use it like this in a formula:

    <code>=Concat(A1:A61,", ")</code>

  3. #3
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging multiple cells (2003)

    Hans

    Sorry but you've lost me on this one !!

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

    Re: Merging multiple cells (2003)

    Hans,
    Do you know if its possible to have the optional argument (StrSep) appear non-bolded in the function pallette? Excels built in functions are easy to distinguish as the compulsory ones are bold and the optional ones are not.
    Regards,
    Rudi

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

    Re: Merging multiple cells (2003)

    Hi Buddy,

    Copy the code Hans sent you, and paste it into Word. Then recopy it from word and open the workbook that contains your codes. Press Alt + F11 and choose INSERT | Module from the VBA Menu Bar. Then paste the code from word into the module.

    Once that is done, select cell B1 and click on the FX button to open the Paste Function Dialog. Choose User Defined from the Categories list and select Concat. Supply the arguments as Hans has mentuioned in his last post.
    Regards,
    Rudi

  6. #6
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging multiple cells (2003)

    can't see User defined in the category list

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

    Re: Merging multiple cells (2003)

    No need to paste into Word first - I didn't use <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags.

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

    Re: Merging multiple cells (2003)

    See screenshot.

    You can also enter the formula yourself, without using the Function Wizard.

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

    Re: Merging multiple cells (2003)

    I don't know if that is possible. You can add descriptions for the arguments of user-defined functions using Laurent Longre's free Funcustomize add-in. See Excel add-ins.

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

    Re: Merging multiple cells (2003)

    Thanks. The download looks interesting. I'll try it sometime!
    Regards,
    Rudi

Posting Permissions

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