I'm not Dana, but you could use it an adjacent cell as another worksheet
function:

=LastGroupOfNumbers(a1)

And drag down the column, well, if your data is in a column.

And you could actually update the values in place (overwriting the original
values) if you wanted via a macro.

Sub testme()
Dim myCell As Range
Dim myRng As Range
Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.Value = LastGroupOfNumbers(.Value)
End With
Next myCell
End Sub

It kind of depends on what you meant...



Biff wrote:

Hi Dana!

How could this be used on a range and without the array elements being
predefined?

Biff

"Dana DeLouis" wrote in message
...
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.



--

Dave Peterson