“Slugify” is a term for converting strings into URL slug format. A URL slug, or just slug in this article’s context, is the part of the URL that comes after the domain extension. In this guide, we're going to show you how to slugify a string with Excel.
A slug typically includes only lowercase letters, numbers, and hyphen characters (“-“). The common use case is to add hyphens between each word, just like spaces.
The title of this article and its URL is a simple example:
| URL: | https://www.spreadsheetweb.com/how-to-slugify-a-string-with-excel/ |
| Slug: | how-to-slugify-a-string-with-excel |
| Title: | How to slugify a string with Excel |
If you only need to replace spaces with hyphens and turn each character to its lowercase counterpart, SUBSTITUTE and LOWER functions are all you need.
If there are multiple unsupported characters, you will need to use a loop for checking each character to see whether they are lowercase and removing repeating characters. Thanks to the LAMBDA function, you can create your own functions without using VBA. Also, you can program these functions to work recursively.
The function to slugify a string
By using the code below, you can slugify a string easily. Create a named range with a name like SLUG, and copy-paste the following formula into the Refers to box.
LET(
character, LOWER(MID(string, ndx, 1)),
charactercode, CODE(character),
iscorrect, OR(AND(charactercode >= 97, charactercode <= 122), AND(charactercode >= 48, charactercode <= 57)),
length, LEN(string),
leftpart, LEFT(string, ndx - 1),
rightpart, RIGHT(string, length - ndx),
IF(ndx > length,
string,
IF(OR(iscorrect, AND(IFERROR(NOT(MID(string, ndx - 1, 1) = "-"), TRUE), NOT(ndx = 1), NOT(ndx = length))),
SLUG(
leftpart & IF( iscorrect, character, "-") & rightpart, ndx + 1),
SLUG3(
leftpart & IF( iscorrect, character, "") & rightpart, ndx)))))
We can also use the LET function, which allow creating names in the function context, to improve this formula. The LET function can be used multiple times instead of bulky formula snippets.
For example, we assigned OR(AND(charactercode >= 97, charactercode <= 122), AND(charactercode >= 48, charactercode <= 57)) part to the name iscorrect, which is used 3 times in the formula.
One other important points about the SLUG function is the ndx argument which refers the index of the characters. Give 1 to the argument to start from the beginning. With each iteration, the ndx value will increase by 1 to check the next character, unless there is a special case like repeating hyphens.
