Combine Cell TextSolved/Closed

Question

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

TrowaD · Accepted Answer

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

Daniel_CCM · Answer

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,

Combine Cell Text

2 responses

Similar discussions

Subscribe To Our Newsletter!