How to slugify a string with Excel
Piblished the April 5, 2020
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
Here is my final formula :