I have a data column that has a heading value with multiple levels, where I only want the first three levels, but I cannot figure out how to get the parsed value?
I was reading this and it shows how to use create a function to return a boolean for the condition, but how would I create a function that would return a parsed value?
This is the Regular Expression that I think I need.
I'm looking for something that would change
1.2.3 and similar for any other header I have that has more than three levels.
Ideally, I'd like to be able to put it into my Query Design as a
Field Expression, but I'm not sure how I would do that.
I assumed your input values could have more than one digit between the dots. In other words, I think you want this ...
? RegExpGetMatch("18.104.22.168.5.", "^(\d+\.\d+\.\d+).*", 1) 1.2.3 ? RegExpGetMatch("22.214.171.124.5.", "^(\d+\.\d+\.\d+).*", 1) 1.27.3
If that is the correct behavior, here is the function I used.
Public Function RegExpGetMatch(ByVal pSource As String, _ ByVal pPattern As String, _ ByVal pGroup As Long) As String 'requires reference to Microsoft VBScript Regular Expressions 'Dim re As RegExp 'Set re = New RegExp 'late binding; no reference needed Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = pPattern RegExpGetMatch = re.Replace(pSource, "$" & pGroup) Set re = Nothing End Function
See also this answer by KazJaw. His answer taught me how to select the match group with
In a query run within an Access session, you could use the function like this:
SELECT RegExpGetMatch([Data Column], "^(\d+\.\d+\.\d+).*", 1) AS parsed_value FROM YourTable;
Note however a custom VBA function is not usable for queries run from outside an Access session.
Try changing your RegEx to
^(\d\.\d\.\d). You need to escape the
. since it has a special meaning in RegExp.