+ Reply to Thread
Results 1 to 7 of 7

[SOLVED] Concatenate Macro

  1. #1
    osaka78
    Guest

    [SOLVED] Concatenate Macro

    Dear friends
    i have a text in cell e9,e10,e11 and e12 is blank
    then text in e13,e14 and e15 is blank
    then text in e16 and e17 is blank ..ect

    how can i create a macro to CONCATENATE all cells before the blanck cell in
    one cell

    regards

  2. #2
    paul
    Guest

    RE: Concatenate Macro

    =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and
    or text between the cell contents
    --
    paul
    remove nospam for email addy!



    "osaka78" wrote:

    > Dear friends
    > i have a text in cell e9,e10,e11 and e12 is blank
    > then text in e13,e14 and e15 is blank
    > then text in e16 and e17 is blank ..ect
    >
    > how can i create a macro to CONCATENATE all cells before the blanck cell in
    > one cell
    >
    > regards


  3. #3
    osaka78
    Guest

    RE: Concatenate Macro

    Dear paul
    i need macro to do Concatenate
    regards

    "paul" wrote:

    > =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and
    > or text between the cell contents
    > --
    > paul
    > remove nospam for email addy!
    >
    >
    >
    > "osaka78" wrote:
    >
    > > Dear friends
    > > i have a text in cell e9,e10,e11 and e12 is blank
    > > then text in e13,e14 and e15 is blank
    > > then text in e16 and e17 is blank ..ect
    > >
    > > how can i create a macro to CONCATENATE all cells before the blanck cell in
    > > one cell
    > >
    > > regards


  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Osaka,

    What Paul gave you was the equivalant of Concatenate, just say
    =E9 & E10 & E11
    etc, . . or
    =E9&E10&E11
    etc, . . or
    =E9 & " " & E10 & " " & E11
    etc, . . or
    =E9&" "&E10&" "&E11
    etc to put spaces between the concatenated cells.

    Do you also have other cells to join? - and if so where are they?

    --

    Quote Originally Posted by osaka78
    Dear paul
    i need macro to do Concatenate
    regards

    "paul" wrote:

    > =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and
    > or text between the cell contents
    > --
    > paul
    > remove nospam for email addy!
    >
    >
    >
    > "osaka78" wrote:
    >
    > > Dear friends
    > > i have a text in cell e9,e10,e11 and e12 is blank
    > > then text in e13,e14 and e15 is blank
    > > then text in e16 and e17 is blank ..ect
    > >
    > > how can i create a macro to CONCATENATE all cells before the blanck cell in
    > > one cell
    > >
    > > regards
    Last edited by Bryan Hessey; 04-09-2006 at 04:36 AM.

  5. #5
    osaka78
    Guest

    Re: Concatenate Macro

    guys i know this but i need MACRO to do this for me

    regards

    "Bryan Hessey" wrote:

    >
    > Osaka,
    >
    > What Paul gave you was the equivalant of Concatenate just say
    > =E9 & E10 & E11
    > etc, . . or
    > =E9&E10&E11
    > etc, . . or
    > =E9 & " " & E10 & " " & E11
    > etc, . . or
    > =E9&" "&E10&" "&E11
    > etc to put spaces between the concatenated cells.
    >
    > Do you also have other cells to join? - and if so where are they?
    >
    > --
    >
    > osaka78 Wrote:
    > > Dear paul
    > > i need macro to do Concatenate
    > > regards
    > >
    > > "paul" wrote:
    > >
    > > > =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want

    > > spaces and
    > > > or text between the cell contents
    > > > --
    > > > paul
    > > > remove nospam for email addy!
    > > >
    > > >
    > > >
    > > > "osaka78" wrote:
    > > >
    > > > > Dear friends
    > > > > i have a text in cell e9,e10,e11 and e12 is blank
    > > > > then text in e13,e14 and e15 is blank
    > > > > then text in e16 and e17 is blank ..ect
    > > > >
    > > > > how can i create a macro to CONCATENATE all cells before the blanck

    > > cell in
    > > > > one cell
    > > > >
    > > > > regards

    >
    >
    > --
    > Bryan Hessey
    > ------------------------------------------------------------------------
    > Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
    > View this thread: http://www.excelforum.com/showthread...hreadid=531289
    >
    >


  6. #6
    Gary''s Student
    Guest

    Re: Concatenate Macro

    Try this macro:


    Sub osaka78()
    Dim s As String
    Dim i As Integer
    Dim k As Long
    k = 65536
    s = ""
    For i = 9 To 11
    s = s & Cells(i, 5)
    Next
    MsgBox (s)
    Cells(k, 1) = s
    s = ""
    For i = 14 To 15
    s = s & Cells(i, 5)
    Next
    MsgBox (s)
    Cells(k - 1, 1) = s
    s = ""
    For i = 16 To 16
    s = s & Cells(i, 5)
    Next
    MsgBox (s)
    Cells(k - 2, 1) = s
    End Sub



    It will concatenate the desired cells and place each concatenated group into
    one cell.


    --
    Gary's Student


    "osaka78" wrote:

    > guys i know this but i need MACRO to do this for me
    >
    > regards
    >
    > "Bryan Hessey" wrote:
    >
    > >
    > > Osaka,
    > >
    > > What Paul gave you was the equivalant of Concatenate just say
    > > =E9 & E10 & E11
    > > etc, . . or
    > > =E9&E10&E11
    > > etc, . . or
    > > =E9 & " " & E10 & " " & E11
    > > etc, . . or
    > > =E9&" "&E10&" "&E11
    > > etc to put spaces between the concatenated cells.
    > >
    > > Do you also have other cells to join? - and if so where are they?
    > >
    > > --
    > >
    > > osaka78 Wrote:
    > > > Dear paul
    > > > i need macro to do Concatenate
    > > > regards
    > > >
    > > > "paul" wrote:
    > > >
    > > > > =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want
    > > > spaces and
    > > > > or text between the cell contents
    > > > > --
    > > > > paul
    > > > > remove nospam for email addy!
    > > > >
    > > > >
    > > > >
    > > > > "osaka78" wrote:
    > > > >
    > > > > > Dear friends
    > > > > > i have a text in cell e9,e10,e11 and e12 is blank
    > > > > > then text in e13,e14 and e15 is blank
    > > > > > then text in e16 and e17 is blank ..ect
    > > > > >
    > > > > > how can i create a macro to CONCATENATE all cells before the blanck
    > > > cell in
    > > > > > one cell
    > > > > >
    > > > > > regards

    > >
    > >
    > > --
    > > Bryan Hessey
    > > ------------------------------------------------------------------------
    > > Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
    > > View this thread: http://www.excelforum.com/showthread...hreadid=531289
    > >
    > >


  7. #7
    Bob Phillips
    Guest

    Re: Concatenate Macro

    Function ConcatFormat(rng1 As Range, ParamArray rng2()) As String
    Dim i As Long
    ConcatFormat = rng1.Text
    For i = LBound(rng2) To UBound(rng2)
    If Not rng2(i) Is Nothing Then _
    ConcatFormat = ConcatFormat & rng2(i).Text
    Next i
    End Function


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "osaka78" <[email protected]> wrote in message
    news:[email protected]...
    > Dear friends
    > i have a text in cell e9,e10,e11 and e12 is blank
    > then text in e13,e14 and e15 is blank
    > then text in e16 and e17 is blank ..ect
    >
    > how can i create a macro to CONCATENATE all cells before the blanck cell

    in
    > one cell
    >
    > regards




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1