Combine Cell Text

Solved/Closed
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
Hi,
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 555
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

Do
    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,
Trowa
Thanks TrowaD,

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

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)


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