Extracting Model Numbers

[jimbo222]

Hi,

I'm looking for a way to extract model numbers from urls. Here is a sample of urls...

Air-King-Alicante-ALI36-36-inRange-Hood.aspx
Air-King-WSV30-Range-Hood-Shelf.aspx
Air-King-SEV30-Range-Hood-Insert.aspx
Air-King-VAL30-Valencia-Wall-Mounted-Range-Hood.aspx
Air-King-Valencia-40-in-Chimney-Extension.aspx
Air-King-Professional-P1848-48-in-Range-Hood.aspx
Air-King-VAL36-Valencia-36in-Wall-Mounted-Range-Hood.aspx
Air-King-CAN36-Cantabria-36in-Wall-Mounted-Range-Hood.aspx
Air-King-Granada-GRAN30-Range-Hood.aspx
Air-King-AV130-Advantage-30in-Under-Cabinet-Range-Hood.aspx

Basically I want extract the alphanumeric model numbers. They are the only substings between dashes that have both letters and numbers. (some have 30in or whatever do designate the size but I can filter these out later or am open for suggestions...maybe if it contains "in" it is ignored)

Any insight would be appreciated. Thanks.

[mikerickson]

A UDF like this should work. You can reject measurments by using the rejectPattern arguments,
as in =ExtractMixed(A1, "*#in*", "*#cm*")

[VBA]Function ExtractMixed(DelimitedString As String, ParamArray RejectionPatterns() As Variant) As String
Dim i As Long
Dim oneSubString As Variant
For Each oneSubString In Split(DelimitedString, "-")
If (LCase(oneSubString) Like "*[a-z]*") And (oneSubString Like "*#*") Then
ExtractMixed = oneSubString
For i = 0 To UBound(RejectionPatterns)
If LCase(oneSubString) Like LCase(RejectionPatterns(i)) Then
ExtractMixed = vbNullString
End If
Next i
If ExtractMixed <> vbNullString Then Exit Function
End If
Next oneSubString
End Function[/VBA]

[jimbo222]

That works perfectly. Thanks so much!