Formula to Convert Product Titles to URL Components

[mattriggs283]

Hello -

This is my first post - so please excuse me (and let me know) if the etiquette isn't correct.

I'm working on a formula to take a product item title and convert it to a URL component.

The URL component has to -
- convert to all lower case
- remove all punctuation
- use a '-' as a separator
- NOT have spaces OR back to back '-'.
- Strip out any punctuation in general (if possible)

Format of Title - Lipton Brisk & Lemon Iced Tea - 12oz.
Ideal URL component - lipton-brisk-lemon-iced-tea-12oz

I'm using Lower & Substitute functions right now. Ultimately, this will live in a spreadsheet template for employees to use and speed up an website entry process. Currently, they key the url component in manually, despite the fact that much of the elements are the same.

I'm hung up the formula on how to treat a '-' already present in the title name. I also don't have anything to remove extraneous other special characters like ampersands.

Here is what I have - =(LOWER((SUBSTITUTE(SUBSTITUTE(B20,"-","")," ","-"))))

This is what it generates 'lipton-brisk-&-lemon-iced-tea--12oz.'

If necessary - just dealing with '.', "&', and '-' already present would be a HUGE help.

Thanks for any assistance you can provide! I've heard great things about this forum from a friend of mine.

 

[Sektor]

UDF:

Function FormURL(cell As Range) As String
    
    Dim re As Object, m As Object
    Dim s As String
    
    Set re = CreateObject("VBScript.RegExp")
    re.IgnoreCase = True
    re.Global = True
    re.Pattern = "\b\w+\b"
    
    For Each m In re.Execute(cell.Value)
        s = s & m.Value & "-"
    Next
    
    Set re = Nothing
    FormURL = Left(s, Len(s) - 1)
    
End Function

 

[mattriggs283]

UDF:

Function FormURL(cell As Range) As String
    
    Dim re As Object, m As Object
    Dim s As String
    
    Set re = CreateObject("VBScript.RegExp")
    re.IgnoreCase = True
    re.Global = True
    re.Pattern = "\b\w+\b"
    
    For Each m In re.Execute(cell.Value)
        s = s & m.Value & "-"
    Next
    
    Set re = Nothing
    FormURL = Left(s, Len(s) - 1)
    
End Function

Thanks Sektor - this worked perfectly. Much appreciated!