Use This Excel Function to Slug-ify / Handle-ize Any Text — Useful for Bulk URL Creation

Periodically I run into the issue where I need to create a lot (thousands) of URLs from random strings of text, which may or may not include capital letters, spaces, hyphens, brackets, commas, etc.

Sometimes this is easiest done in a spreadsheet, so here’s an Excel function I baked up a couple years ago that I use to solve the particular problem of slugifying/handle-izing those URLs. It’s clunky, but functional*. Just replace “A2” with whatever cell you need to slugify, and voila.

*Only works for ASCII characters. Does not check for collisions!

Example

For example, the text

AS dsf a s1111#$ b'lue p"urple AsdoIj

becomes

as-dsf-a-s1111-blue-purple-asdoij

when slugified.

The Function

=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ","-"),"'",""),"""",""),"/",""),"?",""),".",""),">",""),"<",""),",",""),";",""),":",""),"[",""),"]",""),"}",""),"[",""),"{",""),"|",""),"\",""),"+",""),"=",""),"~",""),"`",""),"!",""),"@",""),"#",""),"$",""),"%",""),"^",""),"&",""),"*",""),"(",""),")",""),"#",""),"'",""),"""",""),"---","-"),"--","-"))

If you liked this, follow me on Twitter @elitwilliams.