Concatenate text from columns

  • Thread starter Thread starter Saintsman
  • Start date Start date
S

Saintsman

How can I concatenate text in colB?
ColA has 2 markers C and *
Whenever C is followed by * I need to unite the text fields in ColB
So, for the data below I want
ColA ColB
C Text1 Text1a
C Text2 Text2a Text2b Text2c

ColA ColB
C Text1
* Text1a cont'd
C Text2
* Text2a cont'd
* Text2b cont'd
* Text2c cont'd
C Text3

Hope this makes sense!
 
Try this:

=======
Sub MergeTexts()
'JBeaucaire (12/1/2009)
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
i = 2

Do Until Len(Range("A" & i)) = 0
If Range("A" & i) = "*" And Range("A" & i - 1) = "C" Then
Range("B" & i - 1) = Range("B" & i - 1) & " " & Range("B" & i)
Rows(i).Delete xlShiftUp
Else
i = i + 1
End If
Loop

End Sub
========
 
Back
Top