Concatenate text from columns
 

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!

Concatenate text from columns
 

Thbe easy method is to use a fromula in the worksheet. Something like
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 the
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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158503

Microsoft Office Help

Concatenate text from columns
 

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

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Saintsman" wrote:

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!