Remove non-alphanumeric characters in Google Sheets

I recently found I needed to remove non-alphanumeric characters in Google Sheets. I had a column of text containing business names which I needed to format as URL friendly equivalents. For example, I needed to turn these:

  • My Business Name
  • Derek’s World of Fish!
  • Jane & Janet’s Pieland

Into these:

  • my-business-name
  • dereks-world-of-fish
  • jane-janets-pieland

I achieved this by added the following formula into the top cell of a blank column. This assumes the names you need to format are in the fist column of the spreadsheet

=ArrayFormula( lower(REGEXREPLACE( A2:A , "([^A-Za-z0-9]+)|(---)" , "-" ) ))

Pete Clark

Pete Clark has been working with websites since the 1990s, which seems like a very very long time ago now. You can follow him on Twitter @ClarkCX