Hi. Here's just one way...

Option Explicit
Public RE As RegExp

Function LastGroupOfNumbers(s)
'// Microsoft VBScript Regular Expressions 5.5

Dim Matches As MatchCollection
Const k As String = "(\d+)\D*$"

If RE Is Nothing Then Set RE = New RegExp
With RE
.IgnoreCase = True
.Global = True
.Pattern = k

If .Test(s) Then
Set Matches = .Execute(s)
LastGroupOfNumbers = Matches(0).SubMatches(0)
End If
End With
End Function

Sub TestIt()
Dim v, j
v = Array("W2424", "W2424P", "W3D2412", "W1836PLR", "DW362424L",
"W3D1560DTL")
For j = LBound(v) To UBound(v)
Debug.Print v(j); " - "; LastGroupOfNumbers(v(j))
Next j
End Sub

Returns:
W2424 - 2424
W2424P - 2424
W3D2412 - 2412
W1836PLR - 1836
DW362424L - 362424
W3D1560DTL - 1560

HTH
--
Dana DeLouis
Win XP & Office 2003


"Mr. Me" wrote in message
om...
I have Excel 2000. I would like to get a Regexp function. I have model
numbers that I want to sort. Model numbers are composed of a prefix
(variable
length), the model number (2-6 digits), and a suffix (variable length).
The
left, right and mid functions only work if the prefix is a fixed length,
thus
I need regexp to pick out the first group of 2-6 digits in a row, then
sort
on that.

SO if I had a function that would look at column A (with the whole model
#)
and put the modified, sortable model number in col B, that would be great.

Example model #s and extracted sort key:
W2424 2424
W2424P 2424
W3D2412 2412
W1836PLR 1836
DW362424L 362424
W3D1560DTL 1560

Thanks!

--
Sig: Say no to fixed width HTML tables. They look terrible in most
browsers.