Regular expressions in VB
 

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.

Regular expressions in VB
 

On Thu, 20 Mar 2008 16:04:03 -0700, FiluDlidu
wrote:

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

Regular expressions in VB
 

Input: d301-24b
Output: 024B

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

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

"Ron Rosenfeld" wrote:
It may be simpler using some VBA methods, or a combination.

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

Regular expressions in VB
 

On Thu, 20 Mar 2008 17:12:02 -0700, FiluDlidu
wrote:

Input: d301-24b
Output: 024B

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

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

"Ron Rosenfeld" wrote:
It may be simpler using some VBA methods, or a combination.

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


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

Regular expressions in VB
 

On Thu, 20 Mar 2008 20:59:29 -0400, Ron Rosenfeld
wrote:

On Thu, 20 Mar 2008 17:12:02 -0700, FiluDlidu
wrote:

Input: d301-24b
Output: 024B

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

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

"Ron Rosenfeld" wrote:
It may be simpler using some VBA methods, or a combination.

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


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