Macro for copying and pasting a specific value

[Galitg]

Hi,

I have a column with data that contains campaign id, content id and name id.

I would like to have a macro which will paste only the campaign ID in the column next to the information below, which macro can I use to do so?:

[TABLE="width: 694"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: left"]Data[/TD]
[TD="align: left"]Campaign ID[/TD]
[/TR]
[TR]
[TD="align: left"]&utm_medium=n&utm_campaign=37831323&utm_content=35017178152&AdsetName=9753618089[/TD]
[TD="align: right"]37831323[/TD]
[/TR]
[TR]
[TD="align: left"]&utm_medium=n&utm_campaign=378314324&utm_content=35017172355&AdsetName=9753618089[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]&utm_medium=n&utm_campaign=3783184528&utm_content=35017178152&AdsetName=9753618089[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]&utm_medium=n&utm_campaign=378314678&utm_content=350171785464&AdsetName=9753618089[/TD]
[TD]




[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

If the campaign number is 8 digits, you can use this formula:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:625.43px;" /><col style="width:113.11px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Data</td><td >Campaign ID</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >&utm_medium=n&utm_campaign=37831323&utm_content=35017178152&AdsetName=9753618089</td><td style="text-align:right; ">37831323</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >&utm_medium=n&utm_campaign=378314324&utm_content=35017172355&AdsetName=9753618089</td><td style="text-align:right; ">37831432</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >&utm_medium=n&utm_campaign=3783184528&utm_content=35017178152&AdsetName=9753618089</td><td style="text-align:right; ">37831845</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >&utm_medium=n&utm_campaign=378314678&utm_content=350171785464&AdsetName=9753618089</td><td style="text-align:right; ">37831467</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=MID(A2,SEARCH("campaign=",A2)+9,8)+0</td></tr></table></td></tr></table>

 

[Galitg]

Well, not necessary.. I would like to have something more automatic with a macro.

Are you familiar with any options?

 

[DanteAmor]

Well, not necessary.. I would like to have something more automatic with a macro.

Are you familiar with any options?

Well, try this:

Sub Macro2()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = _
    "=MID(RC[-1],SEARCH(""campaign="",RC[-1])+9,SEARCH(""&"",RC[-1],SEARCH(""Campaign="",RC[-1]))-(SEARCH(""Campaign="",RC[-1])+9))+0"
    .Value = .Value
  End With
End Sub

 

[DanteAmor]

Either way, the formula to extract the ID, regardless of the size of the ID:

=MID(A2,SEARCH("campaign=",A2)+9,SEARCH("&",A2,SEARCH("Campaign=",A2))-(SEARCH("Campaign=",A2)+9))+0 <tbody> </tbody>

<tbody>
</tbody>

Note: if the ID is not numeric, then delete the "+0"

 

[Galitg]

Works great, thanks!

 

[DanteAmor]

Works great, thanks!

I'm glad to help you. Thanks for the feedback.

 

[Galitg]

Sorry, checked again and it does not work, the 4 first numbers are right but the rest aren't.

 

[Galitg]

ok fixed it, thank you!

 

[DanteAmor]

Your welcome!