Google Sheets Formula to Slugify Text

Here’s the only Google Sheets formula that you will need to correctly “slugify” the contents of a cell.

Along with providing the formula, I will also be explaining how it works, in detail. We will use functions like TRIM, REGEXREPLACE, and LOWER to remove leading and trailing spaces, replace sequences of non-alphanumeric characters with hyphens, reduce sequences of multiple hyphens to a single hyphen, and convert text to lowercase.

Let’s take a look…

Convert text to slug in Google Sheets

Now, here’s the formula that you can directly use to convert any text cell to slug, and it works perfectly even if the cell has special characters:

=LOWER(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(TRIM(A2),"[^a-zA-Z0-9]+","-"), "-{2,}", "-"), "^-+|-+$", ""))

I have also added a quick video below showing the same:

Now, let’s break down the formula and see how it works:

  1. TRIM(A2) — The TRIM function removes any leading or trailing spaces from the contents of cell A2.
  2. REGEXREPLACE(…, “[^a-zA-Z0-9]+”, “-“) — The REGEXREPLACE function uses a regular expression pattern to search for and replace specific characters in the text. In this case, the pattern "[^a-zA-Z0-9]+" matches any sequence of one or more characters that are not letters (upper or lower case) or digits. These sequences are replaced with a hyphen character "-".
  3. REGEXREPLACE(…, “-{2,}”, “-“) — This is another use of the REGEXREPLACE function, and it uses the pattern "-{2,}" to match any sequence of two or more consecutive hyphens. These sequences are replaced with a single hyphen character.
  4. REGEXREPLACE(…, “^-+|-+$”, “”) — This is the final use of the REGEXREPLACE function, and it uses the pattern "^-+|-+$" to match any sequence of one or more hyphens at the start of the string "^-+" or at the end of the string "-+$". These sequences are replaced with an empty string.
  5. LOWER(…) — The final step is to use the LOWER function to convert all the letters in the text to lowercase.

So, the overall effect of this formula is to clean up the contents of cell A2 by removing leading and trailing spaces, replacing sequences of non-alphanumeric characters with hyphens, reducing sequences of multiple hyphens to a single hyphen, and finally converting the text to lowercase.

If this is not working for you for some reason, kindly let me know in the comments below

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

3 Comments

  1. Angela Arena says:

    Thank you so much! I love this formula. I’m not very good at sheets. How do you replace the cell A2 with the relative cell to the left of the cell I’m placing that formula in?

  2. Angela Arena says:

    Please disregard my last question. I rememered how to do it. Thank you for this formula!

    1. DeepakNess says:

      Glad you got it working, Angela!