Stripping the Last Field From a URL String

[chasfh]

I get a report from Google AdWords, and the last field in the URL column is my referral code, and I want strip out only the text after the part of the URL that says "ref=".

By example, here are a few URL strings I'm dealing with:

mywebsite.com/default.aspx?ref=oCustomerService
mywebsite.com/default.aspx?ref=oJobFinder
mywebsite.com/jobseeker/search/employerlink.aspx?billingAccountId=10452&ref=oFedex
mywebsite.com/default.aspx?ref=oHelpWanted4
mywebsite.com/default.aspx?ref=omywebsite
mywebsite.com/default.aspx?ref=oPartTimeemployment
mywebsite.com/jobseeker/search/employerlink.aspx?employer=Target&employer_name=Target&employer_id=7816&ref=oTarget
mywebsite.com/jobseeker/search/searchresults.aspx?city=San+Antonio&state=tx&ref=oCityJobSanAntonio
mywebsite.com/jobseeker/search/searchresults.aspx?city=Memphis&state=tn&ref=oCityJobMemphis
mywebsite.com/jobseeker/search/searchresults.aspx?city=New+York&state=ny&ref=ocityNewYork
mywebsite.com/default.aspx?ref=oJobOnline
mywebsite.com/jobseeker/search/employerlink.aspx?employer=Home%20Instead&employer_name=Home%20Instead%20Senior%20Care&ref=oSeniorCare

I want to strip out only the last part so that my answers are:

oCustomerService
oJobFinder
oFedex
oHelpWanted4
omywebsite
oPartTimeemployment
oTarget
oCityJobSanAntonio
oCityJobMemphis
ocityNewYork
oJobOnline
oSeniorCare

I had hoped I could do a text-to-column and separate it out based on "Other", being the entire string "ref=", but "Other" allows only one character. I can't do a standard TTC on "=" because some strings have multiple "="

Is there a handy formula I can use for this? Thank you.

Chuck

[Trebor76]

Hi Chuck,

You have to use the Instr function which is only available in VBA. As such I've created a macro (refer attached) that will place the concatenated list beneath the existing list (tweak the code as requied).

HTH

Robert

[chasfh]

Hi Chuck,

You have to use the Instr function which is only available in VBA. As such I've created a macro (refer attached) that will place the concatenated list beneath the existing list (tweak the code as requied).

HTH

Robert

Hi Robert:

I appreciate the attempt, but unfortunately I am not a VBA programmer. This macro works fine for this sheet, but I was hoping for something I could apply it across all workbooks, and I can't figure out how to do this one.

[Teethless mama]

I get a report from Google AdWords, and the last field in the URL column is my referral code, and I want strip out only the text after the part of the URL that says "ref=".

By example, here are a few URL strings I'm dealing with:

mywebsite.com/default.aspx?ref=oCustomerService
mywebsite.com/default.aspx?ref=oJobFinder
mywebsite.com/jobseeker/search/employerlink.aspx?billingAccountId=10452&ref=oFedex
mywebsite.com/default.aspx?ref=oHelpWanted4
mywebsite.com/default.aspx?ref=omywebsite
mywebsite.com/default.aspx?ref=oPartTimeemployment
mywebsite.com/jobseeker/search/employerlink.aspx?employer=Target&employer_name=Target&employer_id=7816&ref=oTarget
mywebsite.com/jobseeker/search/searchresults.aspx?city=San+Antonio&state=tx&ref=oCityJobSanAntonio
mywebsite.com/jobseeker/search/searchresults.aspx?city=Memphis&state=tn&ref=oCityJobMemphis
mywebsite.com/jobseeker/search/searchresults.aspx?city=New+York&state=ny&ref=ocityNewYork
mywebsite.com/default.aspx?ref=oJobOnline
mywebsite.com/jobseeker/search/employerlink.aspx?employer=Home%20Instead&employer_name=Home%20Instead%20Senior%20Care&ref=oSeniorCare

I want to strip out only the last part so that my answers are:

oCustomerService
oJobFinder
oFedex
oHelpWanted4
omywebsite
oPartTimeemployment
oTarget
oCityJobSanAntonio
oCityJobMemphis
ocityNewYork
oJobOnline
oSeniorCare

I had hoped I could do a text-to-column and separate it out based on "Other", being the entire string "ref=", but "Other" allows only one character. I can't do a standard TTC on "=" because some strings have multiple "="

Is there a handy formula I can use for this? Thank you.

Chuck

Try this:

=MID(A1,FIND("ref=",A1)+4,255)

Drag the Fill Handle down to copy as far as needed

[Trebor76]

Hi Chuck,

Storing the code (macro) into your Personal Macro Workbook or having the original workbook where the code currently is opened at the time, will make it available for use for any workbook.

You have to use the VBA method because the Instr function I have used to do the job is only available via this method.

Teethless_mama clever solution is the way to go for an in-built excel function.

HTH

Robert

[chasfh]

Try this:

=MID(A1,FIND("ref=",A1)+4,255)

Drag the Fill Handle down to copy as far as needed

Now THIS is what I was looking for! Thanks, Mama!

Thank you too, Robert, for your help, I really appreciate it!

[chasfh]

Additional in-cell solutions using FIND nested within MID:

A2: Ceramic Insulators #124-TD45-87
B2: MID(A2,FIND(" #",A2)+1,255)
C2: #124-TD45-87
D2: Returns text after "#" up to 255 chars

A3: Ceramic Insulators #124-TD45-88
B3: MID(A3,1,FIND(" #",A3,1)+255)
C3: Ceramic Insulators #124-TD45-88
D3: Returns text before "#" at least 1 char and after "#" up to 255 chars

A4: Ceramic Insulators #124-TD45-89
B4: Ceramic Insulators
C4: MID(A4,1,FIND(" #",A4,1))
D4: Returns text before "#" at least 1 char