Splitting String in VBA using RegEx
To split a string with a regular expression in VBA:
Public Function SplitRe(Text As String, Pattern As String, Optional IgnoreCase As Boolean) As String()
Static re As Object
If re Is Nothing Then
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.MultiLine = True
End If
re.IgnoreCase = IgnoreCase
re.Pattern = Pattern
SplitRe = Strings.Split(re.Replace(text, ChrW(-1)), ChrW(-1))
End Function
Usage example:
Dim v
v = SplitRe("a,b/c;d", "[,;/]")
lyk
Updated on June 18, 2022Comments
-
lyk almost 2 years
I'm new to VBA and would like to seek some help with regards to using RegEx and I hope somehow can enlighten me on what I'm doing wrong. I'm currently trying to split a date into its individual date, month and year, and possible delimiters include "," , "-" and "/".
Function formattedDate(inputDate As String) As String Dim dateString As String Dim dateStringArray() As String Dim day As Integer Dim month As String Dim year As Integer Dim assembledDate As String Dim monthNum As Integer Dim tempArray() As String Dim pattern As String() Dim RegEx As Object dateString = inputDate Set RegEx = CreateObject("VBScript.RegExp") pattern = "(/)|(,)|(-)" dateStringArray() = RegEx.Split(dateString, pattern) ' .... code continues
This is what I am currently doing. However, there seems to be something wrong during the RegEx.Split function, as it seems to cause my codes to hang and not process further.
To just confirm, I did something simple:
MsgBox("Hi") pattern = "(/)|(,)|(-)" dateStringArray() = RegEx.Split(dateString, pattern) MsgBox("Bye")
"Hi" msgbox pops out, but the "Bye" msgbox never gets popped out, and the codes further down don't seem to get excuted at all, which led to my suspicion that the RegEx.Split is causing it to be stuck.
Can I check if I'm actually using RegEx.Split the right way? According to MSDN here, Split(String, String) returns an array of strings as well.
Thank you!
Edit: I'm trying not to explore the CDate() function as I am trying not to depend on the locale settings of the user's computer.
-
ThunderFrame over 6 yearsnice - but bad results if the string already contains
vbNullChar
characters. -
chikitin over 3 yearshow to return MsgBox ( v)? I get Run-time 13 error. Type mismatch.
-
chikitin over 3 yearsWhat is String(). Is this a function that returns another function of type string?