Merging 2 columns into a new one - but with specifics..

[andybr]

Hi all. I really am a novice with Excel and need some help please

I have a spread sheet full of data (its actually a turbo lister export from eBay). I really need to be able to copy certain data (and keep the original in place) from two columns so that that data lands into a third column but in specific places. I'm hoping and presuming this is relatively simple to conquer?

The attached picture should clarify my non techy explanation !!

Hope someone can help as this will make so much difference.

Cheers Andy

 

[andybr]

Reading a little further - I think its the CONCATENATE funtion - that I could do with help on ! - ta

 

[jindon]

Hi
hope this works

Sub test()
Dim r As Range
With CreateObject("VBScript.RegExp")
     .Pattern = "(DATA FROM)\s[AC]\d+"
     .Global = True
     For Each r In Range("b2",Range("b" & Rows.Count).End(xlUp))
         If .execute(r.Value).Count > 1 Then
         r.Offset(,-1).Value = r.Offset(,-1).Value & vbLf & .execute(r.Value)(0)
         r.Offset(,1).Value = r.Offset(,1).Value & vbLf & .execute(r.Value)(1)
         End If
     Next
End With
End Sub

 

[andybr]

Thanks, but couldnt get it to work (although thats probably more my doing)

It would be nice if I could simply tell it to:

replace text XXXXX with C,1 and replace text YYYYYY with A,1 and repeat down the rows en masse. !!

love excel

 

[jindon]

Then you need to show us your desired result...

 

[andybr]

This shows it as if it was correct.
With the data from A and C transposed into the relevant points of B.

This is a sample and doesnt represent the final spreadsheet (which is actually about 160 columns long)

The unedited text in the B column will always be identical. A and C will just add a title and an image to the finished html

(Sorry if not explaining myself very well )

All help *greatly* appreciated.

Andy

 

[jindon]

How about

Sub test()
Dim r As Range
With CreateObject("VBScript.RegExp")
     For Each r In Range("b2",Range("b" & Rows.Count).End(xlUp))
         .Pattern = "(TITLE)\s\.+[^<]"
         If .test(r.Value) then r.Offset(,-1).Value = .execute(r.Value)(0)
         .Pattern = "(http)\.+[^" & Chr(34) & "]"
         If .test(r.Value) Then r.Offset(,1).Value = .execute(r.Value)(0)
     Next
End With
End Sub

 

[agihcam]

The unedited text in the B column will always be identical. A and C will just add a title and an image to the finished html

based on the above condition, see if this will help.
try;

Sub sample()
Dim i As Long
For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
Cells(i, "b") = "<P align=center><FONT size=3><FONT size=6></FONT><FONT size=6>" & Cells(i, "a") & "
</FONT>
</FONT>

		
	
</P>"
Next
End Sub

 

[jindon]

OOps!
my code is doing opposite.

extracting the title value to A and other to C from col.B....

 

[andybr]

I really appreciate all your help,

I will get back and let you know how I get on.

Thanks again.