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 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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
3
Thanks TrowaD,

Works perfectly
0
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
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,
2
Thanks Daniel, But not what I was after another contributor has provided a solution
0