Home > Excel > Creating an Excel Formula to Encode or Unencode URLS

Creating an Excel Formula to Encode or Unencode URLS

Today I’ve been playing with uncoding paid search URLs that contain multiple redirects from vendor partners for tracking purposes. Since my xls document contains more than 500,000+ rows of data using the find and replace functions is cumbersome and caused excel to freeze. It was time for me to use functions within excel to code what I needed to do with find and replace.

Using the Substitute Function to encode or unencode a URL in Excel

To get the project accomplished on time I embedded multiple substitute functions within each other to essentially replicate find a replace.

The substitute function looks for specified text/value within a cell and will replace with another specified text/value. You can have it do a specific number of times or for all instances within a cell.

=substitute(text, old_text, new_text, [number_of_instances])

=substitute(CELL_with_URL, “what you want removed”, “what you want to replace it with”)

Notice that I didn’t include the # of instances…that means do it for every instance of “what you want removed” within the cell. If you only wanted to remove the first instance of something in the URL do it like this:

=substitute(CELL_with_URL, “what you want removed”, “what you want to replace it with”,1)

In case you ever find yourself in the same boat and need to either encode a URL or decode a URL you can use the following snippets of code within excel.
Please note that you most likely will need to modify this so that it fits whatever characters you are attempting to encode or unencode. 

Excel Formula to Unencode a URL

Take this formula and place it into the cell to the right of whatever needs to be unencoded: 

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL_WITH_URL,”%3F”,”?”),”%20″,” “),”%25”, “%”),”%26″,”&”),”%3D”,”=”),”%7B”,”{“),”%7D”,”}”),”%5B”,”[“),”%5D”,”]”)

 

This will only unencode the following characters: space {}[]&%=

 If there are additional characters that are encoded then they’ll need to be added to the formula. To add additional characters you’ll want to include an additional substitute( to the left of the equals sign and at the end of the string add the additional data that needs to be unencoded. Example to include a $ symbol you would add ,”%24″,”$”  before the final closing parenthesis.

Excel Formula to Encode a URL

 Likewise to encode a URL: Go to the cell to the right of the column that needs to be encoded and use the following:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL_WITH_URL, “%”,”%25″),” “,”%20″),”?”, “%3F”),”&”,”%26″),”=”,”%3D”),”{“,”%7B”),”}”,”%7D”),”[“,”%5B”),”]”,”%5D”)

Advertisements
  1. Thankful user
    June 2, 2013 at 12:24 am

    Thank you! That’s perfect 🙂

  2. January 30, 2015 at 8:47 pm

    This is an awesome tool! I was wondering though if anyone is having trouble with excel converting the “%2A” and instead displaying “%252A”? Does anyone have a solution and/or know how to think it through? Thanks!

    • christijolson
      April 17, 2015 at 11:31 pm

      Great question. Was it a double-encoded URL? If it was then the %25 is the encoding for a % sign, so the %252A would be unencoded to %2A, which when unencoded the second time would be %* ….

  3. christijolson
    April 1, 2015 at 4:57 pm

    I posted this a few years back and came back to it today to copy and paste the formula and found an issue with how i posted this in wordpress. My intention was that users could copy and paste the formula directly out of the blog and into Excel, however since I used the block quotation Excel won’t recognize the quotation marks in the formula! My apologies!
    I’m going to link to a notepad document with the formula so if you open that file you can copy and paste the formula directly into Excel.

  4. Mike
    April 10, 2015 at 1:25 pm

    Did you create link to the notepad doc?\

    • christijolson
      April 17, 2015 at 11:25 pm

      Yes! Sorry. WordPress is a pain in the rear. It won’t let me upload a txt file directly onto the blog. I’ve uploaded it to dropbox and you can access it here: UnEncode-URL.txt

  5. May 6, 2015 at 1:59 pm

    Thanks! I saw your comment about the curly quotes too late. I just used my text editor to do a global find-and-replace of “ and ” with “.

  1. October 25, 2013 at 9:07 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: