Combine Cell Text

ad1959 Posts 10 Registration date Thursday March 6, 2014 Status Member Last seen August 18, 2020 - Feb 10, 2020 at 11:41 PM
 ad1959 - Feb 11, 2020 at 11:25 PM
I am after an easy way to combine text in several rows into one cell.
The number of rows that need combining will vary from 2- 10
(Would be easier if the client who supplies the spreadsheet would learn how to use more than 1 line in a cell).

Column A is a date which is merged over several rows
Column B is over several rows and will vary
Column C is the result required.

Any assistance will be appreciated

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 11, 2020 at 12:17 PM
Hi Ad1959,

The easiest way would be to use a vba code:
Sub RunMe()
Dim fRow, lRow As Integer
Dim cValue

lRow = Range("B" & Rows.Count).End(xlUp).Row
fRow = Range("A" & Rows.Count).End(xlUp).Row

    For Each cell In Range(Cells(fRow, "B"), Cells(lRow, "B"))
        cValue = cValue & cell.Value & "-"
    Next cell
    cValue = Left(cValue, Len(cValue) - 1)
    Range("C" & fRow).Value = cValue
    lRow = fRow - 1
    fRow = Range("A" & fRow).End(xlUp).Row
    cValue = vbNullString
Loop Until fRow = 1

End Sub

To implement:
Alt+F11 to open VBA window, go to top menu Insert > Module. Paste code in the big white field. Close window. Back at the Excel screen Alt+F8 to display available codes and double click RunMe.

Save before running the code, so you can reload your file when you don't like the result.

Best regards,
Thanks TrowaD,

Works perfectly
Daniel Telele Posts 1227 Registration date Tuesday March 7, 2017 Status Member Last seen November 4, 2021   6,277
Feb 11, 2020 at 02:16 AM

You can use the Concatenate function in Excel. Use the function below in the destination cell, then add the columns you want to concatenate.

=CONCATENATE(Column1, Column2, etc)

Thanks Daniel, But not what I was after another contributor has provided a solution