How to slugify a string with Excel

I recently had to work on a database on excel, before importing on my website. So I needed to create new pages on this website with clean urls. The action to transform a string into an url is named “slugify”. Then, you have to define the rules of your future slugs, with or without “-“, or “_”…

Having clean urls for your website is really helpful for the user experience, and it can also impact your SEO on Google.

My goal here is to transform The event's name is "Rock été 2020!" to the-event-s-name-is-rock-ete-2020 (note that my titles were in french so I had to treat the accents on some letters.

Here is my process to slugify my titles with excel :

• First, I delete the accents on some letters;
• Then, I get rid of the special caracters (&;?!/ …), except spaces;
• I don’t want uppercases neither;
• Finally, I delete the spaces not useful (at the beginning, at the end or the repetition of spaces) and I turn the last spaces into “-“.

Function to delete accents with VBA

This fonction is not a native function on Excel. That’s why we will have to add it using VBA. That means I will create a new macro to add my function. Note that you will need to save your document in .xslm or in an other extension that supports macro.

If you don’t know how to create a macro, please refer to this ducumentation.

Here is my function to replace caracters with accents by caracters without accent with VBA

Function stripAccent(thestring As String)
Dim A As String * 1
Dim b As String * 1
Dim i As Integer
Const AccChars = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
For i = 1 To Len(AccChars)
A = Mid(AccChars, i, 1)
b = Mid(RegChars, i, 1)
thestring = Replace(thestring, A, b)
Next
stripAccent = thestring
End Function

Now, you can use the function stripAccent() in your excel sheet.

How to delete special caracters with VBA

This function is neither a native function on excel. So I will add it at the bottom of the previews one.

This is the function I use to delete every special caracters (except spaces) :

Function textEpure(Texte As String) As String
' garde uniquement les lettres et les chiffres
Dim tempmot As String, TempCar As String
For i = 1 To Len(Texte)
TempCar = Mid(Texte, i, 1)
Select Case Asc(TempCar)
Case 48 To 57 'chiffre
Case 65 To 90 'caractères A à Z
Case 97 To 122 'caractères a à z
Case Else
TempCar = " "
End Select
tempmot = tempmot + TempCar
Next i
textEpure = tempmot
End Function

Now, you can use the function textEpure() in your excel sheet.

Get rid of the upper cases with LOWER()

This is another native function of Excel. Lower() will replace uppercases with lowercases.

Delete extra spaces with TRIM()

Excel offers a function to delete extra spaces, TRIM() :

• Spaces at the begining or at the end of a string;
• several spaces following…

Be careful, if you don’t use the english version of Excel, the function will have another name.

Finally, I will replace the last spaces with “-“. For that, I use =SUBSTITUTE(A2," ","-").

Here is my final formula :

=SUBSTITUTE(TRIM(LOWER(textEpure(stripAccent(A2))));" ";"-")

retrouvez tous les outils et ressources en liens avec ce tuto ...

Technologies utilisées pour ce tuto :

Excel

VBA