CODEWITHSUNDEEP
vbaexcel
bm11
bm11

Reputation: 43

Merge text from two cells in Excel into one with VBA

I received many Excel files from a client.

Their system extracted the data into a spreadsheet, but one column is having issues. If the text was too long, it would put the remaining text into the cell below it.

This causes all the other fields in that row to be blank, except for the overflow.

How can I merge cells at issue into one for all files I received?

I uploaded a screen shot of the file as an example. Notice on row 8 that H8 is the only cell. That needs to be merged with H7. Not every row is at issue though.

enter image description here

Upvotes: 1

Views: 2436

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

asuming A is the main (and empty for doubles)
asuming H holds the text
then in L1 and copy down 

=H1&IF(LEN(A2),H2,"")

simplest way (then copy values from L to H and delete empty lines (simply with filter)
when having unknown number of lines (after splitting) you better use vba (or simply repeat the whole procedure till there no empty lines anymore...

doing it in VBA:

Sub testing()
  Dim i As Long
  While Len(Cells(i + 1, 8))
    i = i + 1
    While Len(Cells(i + 1, 1)) = 0 And Len(Cells(i + 1, 8))
      Cells(i, 8) = Cells(i, 8) & Cells(i + 1, 8)
      Rows(i + 1).Delete
    Wend
  Wend
End Sub

most programs skip spaces so you may want to use:

=H1&IF(LEN(A2)," "&H2,"")

or for vba change Cells(i, 8) = Cells(i, 8) & Cells(i + 1, 8) to Cells(i, 8) = Cells(i, 8) & " " & Cells(i + 1, 8)

Upvotes: 3

R3uK
R3uK

Reputation: 14537

This will concatenate the texts in H and delete the row that is not useful :

Sub test_bm11()
Dim wS As Worksheet, _
    LastRow As Long, _
    i As Long

Set wS = ActiveSheet
With wS
    LastRow = .Range("H" & .Rows.Count).End(xlUp).Row
    For i = LastRow To 2 Step -1
        If .Cells(i, "A") <> vbNullString Then
        Else
            .Cells(i, "H").Offset(-1, 0) = .Cells(i, "H").Offset(-1, 0) & .Cells(i, "H")
            .Cells(i, "H").EntireRow.Delete
        End If
    Next i
End With

End Sub

Upvotes: 0

Related Questions