Combine Cell Text [Solved]

Report
Posts
10
Registration date
Thursday March 6, 2014
Status
Member
Last seen
August 18, 2020
-
 ad1959 -
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

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Thanks TrowaD,

Works perfectly
Posts
1241
Registration date
Tuesday March 7, 2017
Status
Member
Last seen
October 15, 2020
3,596
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