Extract data after specific char in a URL string

[mlektra]

Hi, Consider a sheet where I will have a cell in T1 that will have this value :


http://mydomain.com/sp_ups1.php?ite...&czip=&cbskin=8618&cbfid=17238&cbf=K9T5VEGPRY


I will need a formula that will extract the value of :


cbaffi= ---> in U1 cell (or any other cell on the right but in the same row)
czip= ---> in V1 cell (or any other cell on the right but in the same row)
cname= ---> in W1 cell (or any other cell on the right but in the same row)*


The values after the = are always changing but the order of the paramaters in the URL string are always the same.


possible?


* Can we manage to have this cell to capitalize only the 1st letter so, if the data is :


cname=JOE+BEEF


the extracted data will look like : Joe+Beef

 

[RickXL]

Hi,

I can give you a User Defined Function (UDF) that works like a formula.

The code below needs to be pasted into a standard macro module (In the VBA Editor use Insert-->Module).
Assuming the URL is in cell A1, you use it like this:

=getparm($A$1,"cname")

That will return the name. If you choose another string, e.g. "cbaffi" then that value will be returned.
If the parameter is either missing or blank it returns nothing.

Function getParm(URL As String, thisParm As String) As String

    Dim arr As Variant
    Dim arrParm As Variant
    Dim i As Long
    
    arr = Split(URL, "&")
    For i = LBound(arr) To UBound(arr)
        arrParm = Split(arr(i), "=")
        If arrParm(0) = thisParm Then
            getParm = arrParm(1)
            Exit For
        End If
    Next

    If thisParm = "cname" Then
        arr = Split(getParm, "+")
        For i = LBound(arr) To UBound(arr)
            arr(i) = WorksheetFunction.Proper(arr(i))
        Next
        getParm = Join(arr, "+")
    End If
  
End Function

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K	L
1	http://mydomain.com/sp_ups1.php?item=1&cbreceipt=88DQ8JEF&time=1420265625&cbpop=776073B6&cbaffi=FROSNB&cupsellreceipt=88DQ8JEF&cname=DANNY+BOUSQUET&cemail=thatbuddy@gmail.com&ccountry=PF&czip=&cbskin=8618&cbfid=17238&cbf=K9T5VEGPRY
2	88DQ8JEF	1420265625	776073B6	FROSNB	88DQ8JEF	Danny+Bousquet	thatbuddy@gmail.com	PF		8618	17238	K9T5VEGPRY
Sheet1

Cell Formulas
Range	Formula
A2	=getparm($A$1,"cbreceipt")
B2	=getparm($A$1,"time")
C2	=getparm($A$1,"cbpop")
D2	=getparm($A$1,"cbaffi")
E2	=getparm($A$1,"cupsellreceipt")
F2	=getparm($A$1,"cname")
G2	=getparm($A$1,"cemail")
H2	=getparm($A$1,"ccountry")
I2	=getparm($A$1,"czip")
J2	=getparm($A$1,"cbskin")
K2	=getparm($A$1,"cbfid")
L2	=getparm($A$1,"cbf")

 

[AlphaFrog]

U1
=LEFT(MID(T1,FIND("cbaffi=",T1)+7,999),FIND("&",MID(T1,FIND("cbaffi=",T1)+7,999))-1)

V1
=LEFT(MID(T1,FIND("czip=",T1)+5,999),FIND("&",MID(T1,FIND("czip=",T1)+5,999))-1)

W1
=PROPER(LEFT(MID(T1,FIND("cname=",T1)+6,999),FIND("&",MID(T1,FIND("cname=",T1)+6,999))-1))

 

[Rick Rothstein]

U1
=LEFT(MID(T1,FIND("cbaffi=",T1)+7,999),FIND("&",MID(T1,FIND("cbaffi=",T1)+7,999))-1)

I don't know if it is any more efficient or not, but you could also do the above with this formula (shorter, one less function call)...

=REPLACE(LEFT(A1,FIND("&",A1,FIND("cbaffi=",A1))-1),1,FIND("cbaffi=",A1)+6,"")