Split URL after certain text

[lavezzi7]

Hey guys,

I have a couple of URLs that contain the "origin?" text before the part I want to extract. For example:

wwwXXXX.com/ie-de/origin=10_se_Display_native
wwwXXXX.com/ia-de/de/origin=10_se_Display_on
wwwXXXX.com/ia-it/de/origin=10_se_Display_off

I want to create a new column containing the text after "origin=". In this case the new column should contain:

10_se_Display_native
10_se_Display_on
10_se_Display_off

Is there a formula to automatically get that? Please note that neither the part I want to get nor the other part has the same amount of characters among the URLs so I cannot use position-related formulas.

Any kind of help would be very much appreciated!

Thank you in advance!

 

[philwojo]

You could try this, but it assumes that there is nothing after the end of what you have in your examples, it will get up to 50 characters after the "=", it also assumes there is only 1 "=" in the line.

=MID(A1,(FIND("=",A1)+1),50)

The above also assumes your data starts in A1, if different update as necessary.

 

[mumps]

Assuming data starts in cell A1, place this formula in B1 and copy it down: =MID(A1,SEARCH("origin=",A1,1)+7,999)

 

[mumps]

@philwojo: I just thought I would mention that if the url for some reason contains an equal sign before the one associated with the word "origin", your formula will not return the correct value.

 

[philwojo]

yes, I called that out in my post as an assumption, but your code does account for it.

 

[lavezzi7]

Hey guys,

Thanks a lot for the help but the formula does not seem to work i get the following error:

 

[philwojo]

Which formula did you use, and can you paste in exactly what you have.

 

[lavezzi7]

Both formulas had the same result. I created a new sheet and copy pasted a link to A1 just to test.

 

[philwojo]

Can you please post exactly what you are trying to copy and paste in to the cell.

 

[lavezzi7]

http://www.abcdef.com/de-de?origin=1925_cv_de in cell A1.

Both formulas gave the same error.