Regular Expression - String comparsion

[VBA-Snail]

Hi,

I want to match and get the text between word SEVERITY and APPLICATION in the below text using regular expressions.

"SYNTAX_VERSION 6 ADVMONITOR ""CG-DBSPI-3215"" DESCRIPTION ""% of space used on a specific virtual device(M215_VirtDevSpUsdPct)"" SEPARATORS "" "" SEVERITY Normal APPLICATION ""MSSQL Server"" MSGGRP ""DBSPI"" HELPTEXT "" Probable Cause(s): Allocated storage is approaching total storage with the potential impact the inability to extend databases. CONDITION THRESHOLD 99.000000 SETSTART SEVERITY Critical APPLICATION "MSSQL Server" MSGGRP "MSS_Admin" SERVICE_NAME "SQL_Server_<$OPTION(server)>_@@<$MSG_NODE_ID>"

Thanks in Advance

[shg]

Between the first instance of SEVERITY and the last instance of APPLICATION?

Any particular reason to want to use a RegExp other than the snail aspect?

[VBA-Snail]

anything will work though its my part of requirement

[shg]

	A	B
1	"SYNTAX_VERSION 6 ADVMONITOR ""CG-DBSPI-3215"" DESCRIPTION ""% of space used on a specific virtual device(M215_VirtDevSpUsdPct)"" SEPARATORS "" "" SEVERITY Normal APPLICATION ""MSSQL Server"" MSGGRP ""DBSPI"" HELPTEXT "" Probable Cause(s): Allocated storage is approaching total storage with the potential impact the inability to extend databases. CONDITION THRESHOLD 99.000000 SETSTART SEVERITY Critical APPLICATION "MSSQL Server" MSGGRP "MSS_Admin" SERVICE_NAME "SQL_Server_<$OPTION(server)>_@@<$MSG_NODE_ID>"	Normal APPLICATION ""MSSQL Server"" MSGGRP ""DBSPI"" HELPTEXT "" Probable Cause(s): Allocated storage is approaching total storage with the potential impact the inability to extend databases. CONDITION THRESHOLD 99.000000 SETSTART SEVERITY Critical

The formula in B1 is

=Between(A1, "Severity", "Application")

Function Between(sInp As String, sWd1 As String, sWd2 As String) As String
    Dim iPos1 As Long
    Dim iPos2 As Long

    
    iPos1 = InStr(1, sInp, sWd1, vbTextCompare)
    iPos2 = InStrRev(sInp, sWd2, , vbTextCompare)
    If iPos1 > 0 And iPos2 > 0 Then
        Between = Trim(Mid(sInp, iPos1 + Len(sWd1) + 1, iPos2 - iPos1 - Len(sWd1) - 1))
    End If
End Function

[VBA-Snail]

let me explain this bit clearly,

I need the below highlighted in different cells. Output should be Normal (in one cell), Critical (in next cell)

"SYNTAX_VERSION 6 ADVMONITOR ""CG-DBSPI-3215"" DESCRIPTION ""% of space used on a specific virtual device(M215_VirtDevSpUsdPct)"" SEPARATORS "" "" SEVERITY Normal APPLICATION ""MSSQL Server"" MSGGRP ""DBSPI"" HELPTEXT "" Probable Cause(s): Allocated storage is approaching total storage with the potential impact the inability to extend databases. CONDITION THRESHOLD 99.000000 SETSTART SEVERITY Critical APPLICATION "MSSQL Server" MSGGRP "MSS_Admin" SERVICE_N

[shg]

That's why I asked:

Between the first instance of SEVERITY and the last instance of APPLICATION?

[VBA-Snail]

ohh...haven't read properly....hope its clear now.... any way to hit this?

[Leith Ross]

Hello VBA-Snail,

Here is an example macro using Regular Expressions to parse the words into separate cells. It assumes "A1" contains the string. The output is across the columns of the same row.

Sub Macro1()

    Dim Match   As Object
    Dim Matches As Object
    Dim RegExp  As Object
    Dim RngIn   As Range
    Dim RngOut  As Object
    
        Set RngIn = Range("A1")
        Set RngOut = Range("B1")
        
        Set RegExp = CreateObject("VBScript.RegExp")
        RegExp.IgnoreCase = True
        RegExp.Global = True
        RegExp.Pattern = ("severity (\w+) application")
        
            Set Matches = RegExp.Execute(Range("A1"))
            
            For Each Match In Matches
                RngOut.Value = Match.SubMatches(0)
                Set RngOut = RngOut.Offset(0, 1)
            Next Match
            
End Sub