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

andybr

New Member
Joined
Sep 12, 2006
Messages
5
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 !!

spreadsheet.JPG


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

Cheers Andy :)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

andybr

New Member
Joined
Sep 12, 2006
Messages
5
Reading a little further - I think its the CONCATENATE funtion - that I could do with help on ! - ta
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
hope this works
Code:
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

New Member
Joined
Sep 12, 2006
Messages
5
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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

andybr

New Member
Joined
Sep 12, 2006
Messages
5
This shows it as if it was correct.
With the data from A and C transposed into the relevant points of B.

spreadsheet1.JPG


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 :oops:)

All help *greatly* appreciated.

Andy
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

How about
Code:
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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;
Rich (BB code):
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
OOps!
my code is doing opposite.

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

andybr

New Member
Joined
Sep 12, 2006
Messages
5
I really appreciate all your help,

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

Thanks again.
 

Forum statistics

Threads
1,141,916
Messages
5,709,327
Members
421,629
Latest member
RLRobinson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top