Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Concatenate a range (Excel 2002)

    Hi

    Cany anybody tell me the simplest way to Concatenate Cells D3 to CP3, or do I have to do them individually?

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Concatenate a range (Excel 2002)

    You can copy this function into a standard module:

    Function Concat(aRange)
    Dim oCell As Range
    Dim strRes As String
    For Each oCell In aRange
    strRes = strRes & oCell.Value
    Next oCell
    Concat = strRes
    Set oCell = Nothing
    End Function

    Then use the formula

    =Concat(D3:CP3)

    in a cell.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate a range (Excel 2002)

    Hi Hans

    Thank you very much for the function, could I impose on you to adapt it to leave a space between the concatenations,

    Grateful thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    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: Concatenate a range (Excel 2002)

    Change:
    strRes = strRes & oCell.Value

    to:
    strRes = strRes & " " & oCell.Value

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate a range (Excel 2002)

    HI Steve

    My Grateful thanks, could I use a comma instead of a space If I so wished?

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Concatenate a range (Excel 2002)

    Braddy, it is not forbidden to try out very simple things yourself.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate a range (Excel 2002)

    Hi Hans

    Point Taken!

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate a range (Excel 2002)

    Using the addin from Morefunc,

    =MCONCAT(C3:CP3," ")

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate a range (Excel 2002)

    Hi Hans

    You kindly gave this function to concatenate numerous fields in excess of 30, The thing is the numbers in row 1 represent the fixed width
    of the fields in the database to which this data is to be loaded. is there some way to incorporate this in the macro.

    This then has to be output to a fixed width text file.

    Hope this not asking too much.

    Braddy

    Function Concat(aRange)
    Dim oCell As Range
    Dim strRes As String
    For Each oCell In aRange
    strRes = strRes & " " & oCell.Value
    Next oCell
    Concat = strRes
    Set oCell = Nothing
    End Function

    Then use the formula

    =Concat(D3:CP3)
    If you are a fool at forty, you will always be a fool

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

    Re: Concatenate a range (Excel 2002)

    Didn't <post#=505,138>post 505,138</post: > solve your problem?

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate a range (Excel 2002)

    Hi Hans

    When I offered IT the results of the the macro the assumed they were not correct when in fact they were, because they didn't request the headers it looked as if the fields were not there,
    when in fact they were but the cells were empty.

    So my sincere apologies and thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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