Extract text from string

[dawsman]

Some excel wizardry required please.

In cell B1 i have the follwoing data <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>https://example.ca/?utm_source=random1&utm_medium=random2&utm_campaign=random3&utm_term=random4

I am looking to extract the randomx text only, so random1 into b2, random2 into b3, random3 into b4 and random4 into b5

Is there a way of doing his, i have searched the web but haven't seen something appropriate.

Thanks!

 

[Scott Huish]

In B2:
=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B$1,"=",REPT(" ",100)),100*ROWS($B$1:B1),100)),"&",REPT(" ",100)),100))
Copy down to B4

Excel 2010
	B
1	https://example.ca/?utm_source=random1&utm_medium=random2&utm_campaign=random3&utm_term=random4
2	random1
3	random2
4	random3
5	random4
Sheet1

Cell Formulas
Range	Formula
B2	=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B$1,"=",REPT(" ",100)),100*ROWS($B$1:B1),100)),"&",REPT(" ",100)),100))
B3	=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B$1,"=",REPT(" ",100)),100*ROWS($B$1:B2),100)),"&",REPT(" ",100)),100))
B4	=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B$1,"=",REPT(" ",100)),100*ROWS($B$1:B3),100)),"&",REPT(" ",100)),100))
B5	=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B$1,"=",REPT(" ",100)),100*ROWS($B$1:B4),100)),"&",REPT(" ",100)),100))

 

[dawsman]

This works great apart from the last formula which is cutting off early so the full string is not showing e.g. rando instead on random4

 

[Scott Huish]

It didn't do that with your sample text, can you provide sample data of where it didn't work?

 

[dawsman]

Here an example text https://xxxxxxx.xx/?utm_source=xxxxxxxx&utm_medium=xxx&utm_campaign=xxxxxxxxxxxxxxxxxx&utm_term=xx_generic_traffic

returned correct apart from last formula which returned xx_gene and missed the remaining 11 characters

we also have https://www.xxxxxxx.xx/search/boarding/?utm_source=xxxxxxxx&utm_medium=xxx[FONT=Inconsolata, monospace, arial, sans, sans-serif]&utm_campaign=[/FONT]xx_remarketing_all_visitors[FONT=Inconsolata, monospace, arial, sans, sans-serif]&utm_term=[/FONT]xx_remarketing_all_visitors

[FONT=Inconsolata, monospace, arial, sans, sans-serif]returned correctly for formulas 1 & 2 but 3 returned [/FONT]xx_remarketing instead of xx_remarketing_all_visitors and formula 4 returned_all_visitors

Hope that all make sense, thanks.

 

[Scott Huish]

=trim(left(substitute(trim(mid(substitute($b$1,"=",rept(" ",255)),255*rows($b$1:b1),255)),"&",rept(" ",255)),255))

 

[dawsman]

This worked perfectly, thankyou very much!