Two Easy Ways to Encode URLs in Excel

As anyone knows, typing a long list of URL’s can be tedious and time consuming. Copy and pasting from another location is not much better, which leads people to frequently ask us about shortcuts in Excel. One common questions we get from training sessions involving web professionals involves encoding URL’s in Excel. Here are two quick ways to encode URLs inside of Microsoft Excel.

Option One – EncodeURL() Function in 2013

ENCODEURL is a function created specifically for Excel 2013 which returns a URL-encoded string. The formula looks like this:

=encodeurl(URL goes here)

The ENCODEURL is the function and the text for “URL goes here” will usually reference another cell that contains the URL. In the example below the function is used to pass the URL in cell B1 to the formula in B2. You can see the resulting encoded URL output in cell B3:

ENCODEURL in Excel 2013

ENCODEURL in Excel 2013

Click here to read the full EncodeURL formula documentation on Office’s website.

Option Two – 3rd Party Excel Add-Ins

Obviously not everyone is using Excel 2013, so how did people encode URL’s before the latest version of Microsoft Excel debuted? Many people would use a messy SUBSTITUTE formula that looked something like this:

=IF(LEFT(LEFT(SUBSTITUTE(A1,”http://”,””),FIND(“/”,SUBSTITUTE(A1,”http://”,””)&”/”)-1),4)=”www.”,MID(LEFT(SUBSTITUTE(A1,”http://”,””),FIND(“/”,SUBSTITUTE(A1,”http://”,””)&”/”)-1),5,256),LEFT(SUBSTITUTE(A1,”http://”,””),FIND(“/”,SUBSTITUTE(A1,”http://”,””)&”/”)-1))

Not wanting to spend the time or error correction, some people came up with these great Excel Add-Ins to simplify the process.

You can download the URL Tools Excel Add-In through iCrossing or the SEO Tools Add-in through Niels Bosma.

After following the startup instructions, the process works in essentially the same way. Type in your URL, enter the encoding formula, and done.

sdh

URLENCODE for Excel Add-Ins - URL Tools & SEOTools

Other Possible Methods

While we recommend the methods above in most cases, there are a couple other ways to encode URLS through Excel:

Using Visual Basic:

Sevenwires offers a Visual Basic ‘code to encode’ complete with instructions.

Using Substitute Formulas:

Christi Olson shares methods for both encoding and unencoding URLs, showing full formulas like the SUBSTITUTE one mentioned above.

Versitas Admin - Versitas Administrator

2 Responses to “Two Easy Ways to Encode URLs in Excel”

  1. Christi Olson says:

    Thank you for finding my XLS blog post and for linking to it to explain the nested substitution functions.
    I’m glad to see Excel 2013 include the encode and unencode functions! Now I just have to upgrade my Office suite so I don’t have to continue using substitute functions.
    Christi

  2. Harold says:

    I have used http://encodeurl.com/ successfully for this process too. Good points made when using Excel. Thanks!

Leave a Reply