anybody for regular expressions in Excel?

1 post / 0 new

Hi all,

If you use regular expressions, then this may be helpful if you want to use
them as User Defined Functions (UDFs) in Excel.

Enjoy!

Chris

Code starts:

Option Explicit

''' CYates, ICE-BERG Building Physics Jul23

''' Description:

'' This module contains several wrappers for VBScript regular expressions.

'' These provide User Defined Functions (UDF) to make regular expresssions
accessible in Excel.

''' Licence:

'' Creative Commons Attribution Licence 4.0.

'' Thanks must be given to the many contributors to sites such as

'' stackoverflow, mrexcel, vbforums, ozgrid to name but a few.

'' ChatGPT was not used, but feel free to see if it can be improved using
AI.

''' Disclaimer:

'' While reasonable skill and care has been exercised to provide this
software as fit for purpose,

'' we do not guarantee that it is free from defects. The software is
provided "as is" and you may

'' use it at your own risk. We make no warranties as to performance,
merchantability, fitness for

'' a particular purpose, or any other warranties whether expressed or
implied. No oral or written

'' communication from or information provided by us shall create a warranty.
Under no circumstances

'' shall we be liable for direct, indirect, special, incidental, or
consequential damages resulting

'' from the use, misuse, or inability to use this software, even if we have
been advised of the

'' possibility of such damages.

''' Revisions

'' Jul 23 Overhauled regex library for general use. Attempt to make library
useable by general Excel users.

''' Instructions:

'' Ensure the reference MS VBscript regular expressions (version number 5.5
or higher) is available.

'' You can do this by going to Tools/References and searching in the list.
If it is not ticked, scroll

'' down and tick it in the list.

'' The syntax of VBscript regular expressions is very similar to javascript
and others. Resources exist

'' on the internet to assist with building and testing regular expressions.

'' This is a good one: https://www.w3schools.com/jsref/jsref_obj_regexp.asp

'' and this: https://regex101.com/ (set to Java 8)

''' There is a standard layout for functions:

'' Function reXXX (args)

'' Input checks, exit with return value error (usually #N/A) if fail

'' Do stuff and more stuff if required

'' Return value (value can be error if required)

'' End function

''' Function aguments typically take the form: Input string, pattern string,
then optional arguments...

'' ignore case true or false, index of match

Public Function reCount(strInput As String, strPattern As String, Optional
icase As Boolean) As Variant

''' counts regular expressions matching pattern.

''' returns 0 if none found

If strPattern = "" Or strInput = "" Then

reCount = CVErr(xlErrNA)

Exit Function

End If

Dim regex As RegExp

Set regex = New RegExp

Dim matches As Object

With regex

.Global = True

.MultiLine = True

.IgnoreCase = icase

.Pattern = strPattern

Set matches = .Execute(strInput)

reCount = matches.Count

End With

Set matches = Nothing

Set regex = Nothing

End Function

Public Function reTest(strInput As String, strPattern As String, Optional
icase As Boolean) As Variant

''' basic regular expression test

''' returns TRUE on match

If strPattern = "" Or strInput = "" Then

reTest = CVErr(xlErrNA)

Exit Function

End If

Dim regex As RegExp

Set regex = New RegExp

With regex

.Global = True

.MultiLine = True

.IgnoreCase = icase

.Pattern = strPattern

reTest = .Test(strInput)

End With

Set regex = Nothing

End Function

Public Function reVal(strInput As String, strPattern As String, Optional
icase As Boolean, Optional nth As String = "First") As Variant

''' Values for nth can be: First, Last, or a number. The number doesn't have
to be an integer, but will be rounded down to an integer.

If strPattern = "" Or strInput = "" Then

reVal = CVErr(xlErrNA)

Exit Function

End If

Dim regex As RegExp

Set regex = New RegExp

With regex

.Global = True

.MultiLine = True

.IgnoreCase = icase

.Pattern = strPattern

' only execute if at least 1 instance...

If Not .Test(strInput) Then

' instance not found

reVal = CVErr(xlErrNA)

Else ' good to go

Dim match, matches As Object

Set matches = .Execute(strInput)

'' process nth argument

Dim nth_case

Select Case True

Case nth = "" ' no argument supplied

nth_case = 1

Case IsNumeric(nth) ' number supplied. It doesn't have to be
integer but is rounded down

nth_case = CInt(nth)

Case UCase(nth) = "FIRST"

nth_case = 1

Case UCase(nth) = "LAST"

nth_case = matches.Count

Case Else

reVal = CVErr(xlErrNA)

Exit Function

End Select

'' nth higher than match index or index less than 1?

If matches.Count < nth_case Or _

nth_case < 1 Then

reVal = CVErr(xlErrNA)

Exit Function

Else ' all good!

reVal = matches(nth_case - 1).Value

End If

Set matches = Nothing

End If

End With

Set regex = Nothing

End Function

Public Function reSub(strInput As String, strPattern As String, strRep As
String, Optional icase As Boolean) As Variant

''' replaces all instances of pattern with a new string

'' it would be nice to choose the specific index to replace instead of all
instances, but this is probably a job for the index function

'' this replaces the legacy function reReplace and is synonymous with
Excel's SUBSTITUTE function with comparable functionality.

If strPattern = "" Or strInput = "" Then

reSub = CVErr(xlErrNA)

Exit Function

End If

Dim regex As RegExp

Set regex = New RegExp

With regex

.Global = True

.MultiLine = True

.IgnoreCase = icase

.Pattern = strPattern

' ' only execute if at least 1 instance...

' If Not .Test(strInput) Then

' ' instance not found

' ' Neither VBscript or Excel SUBSTITUTE evaluates this as an
error.

' ' However, for the sake of consistency with the other UDFs

' ' we could evaluate it to an error in this case.

' reSub = CVErr(xlErrNA)

'

' Else ' good to go

''' code removed...

'' ...to be more consistent with Excel SUBSTITUTE

reSub = .Replace(strInput, strRep)

' End If

End With

Set regex = Nothing

End Function

Public Function reFind(strInput As String, strPattern As String, Optional
icase As Boolean, Optional nth As String = "First") As Variant

''' Values for nth can be: First, Last, All (not yet implemented), or a
number. The number doesn't have to be an integer, but will be rounded down
to an integer.

'' this replaces the legacy function "reIndex" and has changes in
functionality: new nth option, returns first match by default instead of
last and index position

'' now matches Excel's FIND function.

If strPattern = "" Or strInput = "" Then

reFind = CVErr(xlErrNA)

Exit Function

End If

Dim regex As RegExp

Set regex = New RegExp

With regex

.Global = True

.MultiLine = True

.IgnoreCase = icase

.Pattern = strPattern

' only execute if at least 1 instance...

If Not .Test(strInput) Then

' instance not found

reFind = CVErr(xlErrNA)

Else ' good to go

Dim match, matches As Object

Set matches = .Execute(strInput)

'' process nth argument

Dim nth_case

Select Case True

Case nth = "" ' no argument supplied

nth_case = 1

Case IsNumeric(nth) ' number supplied. It doesn't have to be
integer but is rounded down

nth_case = CInt(nth)

Case UCase(nth) = "FIRST"

nth_case = 1

Case UCase(nth) = "LAST"

nth_case = matches.Count

Case Else

reFind = CVErr(xlErrNA)

Exit Function

End Select

'' nth higher than match index or index less than 1?

If matches.Count < nth_case Or _

nth_case < 1 Then

reFind = CVErr(xlErrNA)

Exit Function

Else ' all good!

reFind = matches(nth_case - 1).FirstIndex + 1 ' plus 1 to
match functionality of FIND

End If

Set matches = Nothing

End If

End With

Set regex = Nothing

End Function

Chris Yates2's picture
Offline
Joined: 2011-10-02
Reputation: 0