I have a URL of the following form:
http://www.example.com/page.html?param1=asdf¶m2=asdfg¶m3=asdfgh
I want to extract the value of param2 or 3 from the URL as well as remove that particular param from the URL. Any ideas on how to do this using Excel?
Best Answer
I don't believe Excel has built-in functions for handling URLs, so you'll have to resort to creatively combining regular string manipulation functions like
LEN(),MID()andSEARCH().Assuming your URL is in cell
A1and the parameter whose value you want to extract is in cellB1, try the following formulae.To get the value of the given parameter:
To remove the parameter and its value from the URL:
Note: depending on your regional settings, you may have to replace all the semicolons in the formulae with commas.
Detailed explanation
To get the value of the given parameter, we first need to locate it within the URL:
The
SEARCH()function locates the first parameter (the given parameter) within the second parameter (the URL) and returns the number of the starting position where it occurs. Note that we append the equal sign to the name of the parameter, to be sure we're searching for the correct thing. Otherwise searching forparam1might instead return the location of, say,param10if it occurs earlier in the URL.With the parameter found, we need to return the portion (or substring) of the URL starting from where the parameter's value begins and ending right before the next ampersand. To do that, we use the
MID()function, which takes three parameters: the string from which to return the substring, the position at which to start, and the number of characters to return.We're also using the
LEN()function, which simply returns the length of the given string (in this case the URL). This is just a placeholder for now (the third parameter didn't become optional until Excel 2010), but will come in handy later when we want the value of the last parameter.First we need to move the starting position from the beginning of the parameter itself to where its value begins. To do so, we add to the parameter's location within the string its length, as well as 1 (for the
=sign).That's better, the value returned starts at the correct location. To get it to stop at the correct location, we need to locate the next ampersand sign, which signifies where the next parameter starts.
We're using the
SEARCH()function again, this time adding a third parameter specifying the position to start the search at - we're not interested in ampersands preceding the given parameter, only those after it.To get the length of the parameter's value from the above, we need to subtract the position where the parameter begins, the length of the parameter, and again 1 for the
=sign.This works for all parameters except the last, because there's no trailing ampersand at the end of the URL. For that case, we can use the
IFERROR()function to detect the error that Excel gives, and return some fixed number instead. A good choice isLEN(A1)- the length of the string is guaranteed to be greater than the length of any of its substrings, and if we pass this as the third argument toMID(), it'll return all characters from the given position to the end of the string, which is just what we need.Combining this with the above, we get the following formula:
One last thing: if
B1contains a parameter that doesn't exist in the URL, the above returns a#VALUEerror. To return an empty string (or any other appropriate value) in such a case, wrap the whole thing in anotherIFERROR():The formula to remove the parameter and its value from the URL is pretty similar, using the
REPLACE()function to replace a given substring of the URL (which is defined more or less the same way as above) with an empty string.You can probably streamline the formulae a bit by moving commonly used blocks (like
SEARCH(B1 & "="; A1)) to their own columns and referencing those cells instead of typing out the formula several times. Those additional columns can then be hidden from view.