How to convert string to URL slug in Excel?

In this Excel tutorial, you will learn how to convert strings to URL slug or format.

This URL slug will not have Stop Words.

This will be done using Excel VBA.

Use cases of converting string to URL slug in Excel

Some of the use cases are –

• Suppose you have a list of post topics or sub-topics in Excel. You want to quickly get the URL slug of these topics or sub-topics
• Reverse engineering URL link of products listed on E-commerce site. 

In the below image you can see a table of product lists.

You can see the pattern of URL domain/category/product-name/pid

Using URL format VBA UDF  and concatenation you can quickly reverse engineer the URL of all the products in the list.

Removing stop words from a string in Excel

The very first step to convert a string to URL slug is to remove stop words from it.

What are stop words?

In web technology, stop words are those words that are excluded from a string while artificially knowing the meaning of a string by web crawlers. 

These stop words are considered to have very little impact on the meaning of a string if excluded from the string.

Example:- a, an ,the, in, to ,are, is are some of the stop words.

To remove stop words use the below VBA function or UDF. 

To use the below VBA UDF just create a new standard module in VBA IDE then copy-paste the below code section.

How to insert and run VBA code in Excel?

Function f_removeStopWords(str As String) As String
  Dim lStr As String, ele As Variant
  Dim ar() As String, nwStr As String
  lStr = Application.WorksheetFunction.Trim(LCase(str))
  ar = Split(lStr) ''default delimiter is space
  For Each ele In ar
     If f_isStopWord(CStr(ele)) = False Then
        nwStr = nwStr & " " & CStr(ele)
     End If
  Next ele
  f_removeStopWords = Application.WorksheetFunction.Trim(nwStr)
End Function
Private Function f_stopwords() As Variant()
  f_stopwords = Array("a", "about", "actually", "almost", "also", "although", _
  "always", "am", "an", "and", "any", "are", "as", "at", "be", "became", _
  "become", "but", "by", "can", "could", "did", "do", "does", "each", _
  "either", "else", "for", "from", "had", "has", "have", "hence", "i", _
  "if", "in", "is", "it", "its", "just", "may", "maybe", "me", "might", _
  "mine", "must", "my", "neither", "nor", "not", "of", "oh", "ok", "or", _
  "our", "the", "their", "when", "whenever", "where", "whereas", "wherever", _
  "whether", "which", "while", "who", "whoever", "whom", "whose", "why", "will", _
  "with", "within", "without", "would", "yes", "yet", "you", "your", _
  "being", "he", "on", "so", "to", "too", "he'd", "he'll", "her", "here", "here's")
End Function
Private Function f_isStopWord(str As String) As Boolean
   Dim pos As Integer
   pos = 0
   On Error Resume Next
   pos = Application.Match(str, f_stopwords, 0)
   If pos = 0 Then
     f_isStopWord = False
   Else
     f_isStopWord = True
   End If
End Function

In the above code section f_stopwords is a function having a list of stop words, you can add remove stop words as per your requirement.

f_removeStopWords is the VBA UDF function which will remove stop words from a string.

Cell A2 formula =f_removeStopWords(A1)

As you can see stop words like “to” and “in” are removed from a string.

Convert string to URL slug in Excel

After removing stop words we get a new string.

In this new string, each word should be separated by hyphen (-) to make a URL slug.

Paste this below code section in the same module which you have created for the f_removeStopWords function.

UDF function f_urlFormat is the function that will convert a string to URL slug.

Code line trmLcsStr = f_removeStopWords(str) will remove stop words then this new string without stop words will get converted into a URL slug.

Function f_urlFormat(str As String) As String
  Dim trmLcsStr As String, sLen As Integer
  Dim cd As Byte, i As Integer, sbStr As String
  trmLcsStr = f_removeStopWords(str)
  trmLcsStr = Application.WorksheetFunction.Trim(LCase(trmLcsStr))
  sLen = Len(trmLcsStr)
  For i = 1 To sLen
      sbStr = Mid(trmLcsStr, i, 1)
      cd = Asc(sbStr)
    If ((cd < 97 Or cd > 122) And sbStr <> "-" And Not (IsNumeric(sbStr))) Then
       trmLcsStr = Application.WorksheetFunction.Substitute(trmLcsStr, sbStr, "-", 1)
    End If
  Next i
  Do While Len(trmLcsStr) <> Len(Application.WorksheetFunction.Substitute(trmLcsStr, "--", "-"))
    trmLcsStr = Application.WorksheetFunction.Substitute(trmLcsStr, "--", "-")
  Loop
  If Right(trmLcsStr, 1) = "-" Then
   f_urlFormat = Left(trmLcsStr, Len(trmLcsStr) - 1)
  Else
   f_urlFormat = trmLcsStr
  End If
End Function

Cell A2 formula =f_urlFormat(A1)

Download the text file having all the VBA code for URL slug conversion

VBA UDF code to convert string into URL format

Suggested readings

ByVal And ByRef Difference in VBA With Example

How to prevent duplicate entries in Excel? | Allow only unique values entries in Excel