Regexp needed in Excel 2000

[Mr. Me]

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!

 

[Dana DeLouis]

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

 

[Biff]

Hi Dana!

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

Biff

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]

Hi. Sorry about the test sub. Just use it like a regular function on the
worksheet.

=LastGroupOfNumbers(A1)

I think this code can be made more efficient, but I just don't see it at the
moment... :>(
The reason I pulled RE out of the function was in case you wanted to use it
in a macro. This way, RE wouldn't be initialized with each call.
HTH
--
Dana DeLouis
Win XP & Office 2003

Hi Dana!

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

Biff

 

[Dave Peterson]

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...

Hi Dana!

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

Biff

 

[Biff]

Hi!

I get a compile error:

User-defined type not defined

With this part highlighted:

RE As RegExp

Biff

 

[Dana DeLouis]

I get a compile error:

User-defined type not defined

Hi Biff.
In the vba editor, go to Tools | References.. |
and look for something similar to:

"Microsoft VBScript Regular Expressions 5.5"
Check this to add it to vba.

I see you have Excel 2000. Hopefully, it is there as I don't remember
anymore. :>(

 

[Biff]

OK, got it.

Works like a charm!

Thanks for your patience.

Biff

 

[Biff]

Hmmm....

Just as aside, many, many years ago I did a *little* programming in C and if
you needed to compile a library file (header file)with your code you simple
added a line of code to do that. Seems to me it would save a lot of what I
just experienced if VB(A) had this same capability. Or, maybe it already
does, I know hardly anything about VBA.

Biff

 

[Mr. Me]

Hi Biff.
In the vba editor, go to Tools | References.. |
and look for something similar to:

"Microsoft VBScript Regular Expressions 5.5"
Check this to add it to vba.

If VBScript Regex is not in the list of references already, add the dll c:
\winnt\system32\vbscript.dll. That's what I had to do.

 

[Mr. Me]

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

Does the LastGroupOfNumbers function have to be global or declared globally?
I have a reference to Microsoft VBScript Regex 5.5 under References, but in
the worksheet I get this error "#NAME". Column B4 is my part number, column
A4 is using the function like this: =LastGroupofNumbers(B4)

What could I be doing wrong here?

Thanks for your help.
Chuck

 

[Dave Peterson]

I just copied Dana's code and pasted into a General module (not behind a
worksheet and not behind ThisWorkbook). Then I added that reference and it
worked fine for me.

Did you put it in a General module?

 

[Mr. Me]

I just copied Dana's code and pasted into a General module (not behind a
worksheet and not behind ThisWorkbook). Then I added that reference and it
worked fine for me.

Did you put it in a General module?

I don't know. But when I hit alt-F11 it opened a VBA window, which happened
to be in personal.xls, which holds all my global macros. I don't know why it
opened personal.xls since my real worksheet had the focus at the time. So the
first time I pasted it there. So I deleted it from personal.xls and pasted it
into my real workbook and it seems to be working.

Thanks.

 

[Dana DeLouis]

... So I deleted it from personal.xls and pasted it

into my real workbook and it seems to be working.

Hi. Glad it's working :>) Just some added info. Since you placed that
function into another workbook, and that workbook was not an Add-Inn, then
you need to point to the workbook that has that function. For example, I
believe the following would have worked in your case.

=PERSONAL.XLS!LastGroupOfNumbers(A1)

A good technique is to have Excel enter the function for you. This helps as
a check also when writing new functions.
On the worksheet, chick the "Insert Function button next to the formula bar
(Fx)
Select the "User Defined" category.
Now, look for your function. This will enter the complete reference.
If you don't find your function, then it may be a clue that the function was
placed incorrectly on a worksheet module, so something similar.
HTH. :>)

 

[Dave Peterson]

And just to add to Dana's response...

You may want to keep the code in your personal.xls workbook. Then you'll be
able to use this function in any workbook you open (follow Dana's syntax,
though).

But if you share the workbook with this formula in it, it could get messy. (I'd
convert the formulas to values before I share.)