Extracting UTM parameters from URLs

[anpassung]

Hello,

I am trying to extract the UTM parameters from my Google Analytics URLs with a formula in Excel. I looked on the forum & online but the solutions are for only 1 parameter & I would like to extract them into different columns. Also, I would like JUST the parameter & not umm_xxx. Is there a way to do this? I can pull out what is after the = but my skills are limited to pull out the second or more parameters.

https://www.test.com/test?hp=header&utm_expid=.rv5U4323Tv-MgchhoEB9Dw.0&utm_referrer=

https://www.test.com/us/test?utm_ex...B9Dw.0&utm_referrer=https:643/www.google.com/

https://www.test.com/test?utm_

Thanks for any help,

Adj

 

[DanteAmor]

Hi and welcome to the board!

Try this

Book1
	A	B	C
1
2	https://www.test.com/test?hp=header&utm_expid=.rv5U4323Tv-MgchhoEB9Dw.0&utm_referrer=	header&utm_expid	.rv5U4323Tv-MgchhoEB9Dw.0&utm_referrer
3	https://www.test.com/us/test?utm_expid=.nv5U6321fv-MgchhoEB9Dw.0&utm_referrer=https:643/www.google.com/	.nv5U6321fv-MgchhoEB9Dw.0&utm_referrer	https:643/www.google.com/
4	https://www.test.com/test?utm_
sheet

Cell Formulas
Range	Formula
B2:C4	B2	=TRIM(MID(SUBSTITUTE($A2,"=",REPT(" ",250)),250*COLUMNS($B$1:B1),250))

 

[anpassung]

hi there, thanks but it isn't exactly right.
In the test above - I would be extracting
1st url: ".rv5U4323Tv-MgchhoEB9Dw.0" & " " (blank )
2nd: ".nv5U6321fv-MgchhoEB9Dw.0" & "https:643/www.google.com/"
3rd " " (blank again )

Is there a way or just a pipe dream?

 

[DanteAmor]

You can put the expected result of each of the examples.
If you have more scenarios it is better to put them here.

 

[anpassung]

https://www.test.com/test?hp=header&utm_expid=.rv5U4323Tv-MgchhoEB9Dw.0&utm_referrer=	.rv5U4323Tv-MgchhoEB9Dw.0
https://www.test.com/us/test?utm_expid=.nv5U6321fv-MgchhoEB9Dw.0&utm_referrer=https:643/www.google.com/	.nv5U6321fv-MgchhoEB9Dw.0	https:643/www.google.com/

Hi again, thanks for your help. Is this better?

 

[DanteAmor]

What are the patterns?
In the first example you get the data after the second sign "="
In the second example you get the data after the first sign "="

Could it be that the first parameter is after the characters "=."?

How many parameters can exist?

 

[anpassung]

Yes, there are a few different utm parameters for URLs that are used in marketing — utm_referrer= or utm_source=

so I need to put the parameters in their own columns

 

[anpassung]

I hope that was clear. Yes, all the utm parameters end with a =.

 

[DanteAmor]

How about in cell B2, Copy to the right and down

Book1
	A	B	C	D
2	https://www.test.com/test?hp=header&utm_expid=.rv5U4323Tv-MgchhoEB9Dw.0&utm_referrer=	.rv5U4323Tv-MgchhoEB9Dw.0
3	https://www.test.com/us/test?utm_expid=.nv5U6321fv-MgchhoEB9Dw.0&utm_referrer=https:643/www.google.com/	.nv5U6321fv-MgchhoEB9Dw.0	https:643/www.google.com/
sheet

Cell Formulas
Range	Formula
B2:D3	B2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"&",""),"utm_expid=",REPT(" ",250)),"utm_referrer=",REPT(" ",250)),COLUMNS($B1:C1)*250-249,250))

 

[anpassung]

Hi again,

That works really well for these urls - thanks so much. I really need to develop my skills as this has a couple functions I need to learn to use.

Have a great weekend!