Regular expressions in VB

[FiluDlidu]

Hi all,
Mike H. just gave me a very nice introduction to modules (see around 15
threads below this one: "Remove any letter from a referenced cell") and
actually showed me that regex could be used with Excel.

Now I'm craving to get it working even more efficiently:

Is there a way to play with the matched expression and modify it in the
output?

Example: matching string could be "-\d{1,}[a-zA-Z]*$", but with an output in
which:
- the dash would be dropped;
- one or two zeroes would be added before the number if less than three
digits are found, so that the format is what we know as "000" in regular
Excel;
- and any lower letter matched is returned in upper case.

Thanks for any time one would spend thinking about this.

 

[Ron Rosenfeld]

Hi all,
Mike H. just gave me a very nice introduction to modules (see around 15
threads below this one: "Remove any letter from a referenced cell") and
actually showed me that regex could be used with Excel.

Now I'm craving to get it working even more efficiently:

Is there a way to play with the matched expression and modify it in the
output?

Example: matching string could be "-\d{1,}[a-zA-Z]*$", but with an output in
which:
- the dash would be dropped;
- one or two zeroes would be added before the number if less than three
digits are found, so that the format is what we know as "000" in regular
Excel;
- and any lower letter matched is returned in upper case.

Thanks for any time one would spend thinking about this.

It may be simpler using some VBA methods, or a combination.

But I need to see examples of input and desired output.
--ron

 

[FiluDlidu]

Input: d301-24b
Output: 024B

Input: 487-34-1ABcd
Output: 001ABCD

Input: 71d-95T-345
Output: 345

 

[Ron Rosenfeld]

Input: d301-24b
Output: 024B

Input: 487-34-1ABcd
Output: 001ABCD

Input: 71d-95T-345
Output: 345

Well, here's one way, using a combination of regular expressions and VBA
functions, assuming I've interpreted your specifications correctly.

The regex pulls out the final "word" and separates the initial digits from the
ending (and optional) non-digits.

Then VBA does the formatting and Case changing.

=========================
Option Explicit
Function LastPart(str As String)
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\d+)(\w*)$"
If re.test(str) = True Then
Set mc = re.Execute(str)
LastPart = Format(mc(0).submatches(0), "000") & UCase(mc(0).submatches(1))
End If
End Function
====================================
--ron

 

[Ron Rosenfeld]

Well, here's one way, using a combination of regular expressions and VBA
functions, assuming I've interpreted your specifications correctly.

The regex pulls out the final "word" and separates the initial digits from the
ending (and optional) non-digits.

Then VBA does the formatting and Case changing.

=========================
Option Explicit
Function LastPart(str As String)
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\d+)(\w*)$"
If re.test(str) = True Then
Set mc = re.Execute(str)
LastPart = Format(mc(0).submatches(0), "000") & UCase(mc(0).submatches(1))
End If
End Function
====================================
--ron

One minor change.

The problem is to return a blank if the referenced cell is blank, so:

================================
Option Explicit
Function LastPart(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\d+)(\w*)$"
If re.test(str) = True Then
Set mc = re.Execute(str)
LastPart = Format(mc(0).submatches(0), "000") & UCase(mc(0).submatches(1))
End If
End Function
===================================
--ron