Search code examples
excelstring-concatenation

EXCEL concatenate all text in cells between two borders

In my MS Excel spreadsheet, the Border is used to determine the end of some related data. I want to convert these related cell`s content into one cell. I want to do it by concatenate the texts inside cells of column until Border and begin with next cell and continue concatenating until next Border and so on . . . . Is there a trick for doing this? enter image description here

Thanks in advance

Solution

  • There is a tons of thing you can do with this but I think you can get the hang of it. It is hardcoded to scan the first column, and output into the second one - you can parameterize it obviously. 

    Sub concat()

' loop on first column but this could be an input
Dim max_rows As Integer
Dim start_col As Integer ' The column where your data is
start_col = 1
max_rows = ActiveSheet.UsedRange.Rows.Count ' count how many times to loop
Dim counter As Integer
counter = 1 ' this is for the output to know when we wrote out something we increment to next cell
Dim temp_string As String
temp_string = ""  ' variable to store until write out
For i = 1 To max_rows:
    Cells(i, 1).Select
    temp_string = temp_string + " " + ActiveCell.Value
    If Selection.Borders(xlEdgeBottom).LineStyle = 1 Then
        out = temp_string
        Cells(counter, 2) = out
        counter = counter + 1
        temp_string = ""
End If
Next i
End Sub