Concatenate text from columns

[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!

 

[joel]

Thbe easy method is to use a fromula in the worksheet. Something lik
this in cell C1

=if(A2="*",B1&B2,"")


Then copy formula down column C

Next Copy column C and PasteSpecial using Values.

Finally sort sheet using column A. The rows with column A = C are th
final results

Sub Combinerows()

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("C1").Formula = "=if(A2=" * ",B1&B2,"")"
Range("C1").Copy _
Destination:=Range("C1:C" & LastRow)

Range("C1:C" & LastRow).Copy
Range("C1:C" & LastRow).PasteSpecial Paste:=xlPasteValues

Rows("1:" & LastRow).Sort _
Header:=xlNo, _
Key1:=Range("A1"), _
order1:=xlAscending

End Su

 

[JBeaucaire]

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
========