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:
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:
Not wanting to spend the time or error correction, some people came up with these great Excel Add-Ins to simplify the process.
After following the startup instructions, the process works in essentially the same way. Type in your URL, enter the encoding formula, and done.
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.