VBA extract all fieldname within a string

Question

I have a string like this one :

"'where CAST(a.DT_NPE_SORTIE as integer ) < cast (add_months (cast (a.dt_nep_restructuration as date format 'YYYYMMDD'), 12) as integer) and a.DT_NPE_SORTIE is not null and a.DT_NPE_SORTIE <> '99991231' and a.dt_npe_restructuration is not null and a.dt_npe_restructuration <> '99991231'"

I need to extract all "a.FIELDNAME" like a.dt_nep_restructuration, a.DT_NPE_SORTIE from the previous screen.

I need to do this in VBA for a project at work.

So far I used If & InStr to check if a list of value is present in the string. But it will be easier for me to extract all a.FIELDNAME then check if they match with fieldname in an array.

Best regards,

jouvzer

You can probably use [regular expressions](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) for this. — Ron Rosenfeld, Mar 23 '21 at 10:29
I saw there are many with "NPE" and one with "NEP"? Is that a typo? If it is, then will it always start with `a.dt_nep_...`? — Siddharth Rout, Mar 23 '21 at 10:54
No it's a typo in order to raise an error for my vba function. It will always start with "a." — Jouvzer, Mar 23 '21 at 10:57

score 5 · Accepted Answer · answered Mar 23 '21 at 11:02

I saw there are many with "NPE" and one with "NEP"? Is that a typo? If it is, then will it always start with a.dt_nep_...? – Siddharth Rout 9 mins ago

No it's a typo in order to raise an error for my vba function. It will always start with "a." – Jouvzer 6 mins ago

I have handled both NPE/NEP. Is this what you are trying?

Option Explicit

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "a.dt_(nep|npe)_\w+"
    Dim regEx As Object
    Dim strInput As String
    Dim inputMatches As Object
    Dim i As Long
    
    strInput = "'where CAST(a.DT_NPE_SORTIE as integer ) < cast (add_months (cast (a.dt_nep_restructuration as date format 'YYYYMMDD'), 12) as integer) and a.DT_NPE_SORTIE is not null and a.DT_NPE_SORTIE <> '99991231' and a.dt_npe_restructuration is not null and a.dt_npe_restructuration <> '99991231'"
        
    Set regEx = CreateObject("VBScript.RegExp")
        
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPattern
    End With
        
    Set inputMatches = regEx.Execute(strInput)
    
    If inputMatches.Count <> 0 Then
        For i = 0 To inputMatches.Count - 1
            Debug.Print inputMatches.Item(i)
        Next i
    End If
End Sub

Note: If it starts with a.dt then you can also use a.dt_\w+_\w+

shrivallabha.redij · Answer 2 · 2021-03-23T13:34:23.970

3

Here is one RegExp based code which will print output for cell A1 in immediate window.

Public Sub FindMatches()
    Dim oRgEx As Object, oMatches As Object
    Dim i As Long
    Set oRgEx = CreateObject("VBScript.RegExp")
    With oRgEx
        .Global = True
        .MultiLine = True
        .Pattern = "\ba\.[A-z_]+\b"
        Set oMatches = .Execute(Range("A1").Value)
        If oMatches.Count <> 0 Then
            For i = 0 To oMatches.Count - 1
                Debug.Print oMatches.Item(i)
            Next i
        End If
    End With
End Sub

Depending on your actual data, you can adjust this further and adapt in your code.

Note: I am a basic level user of RegExp so you may want to consider suggestions indicated below such as \ba\.[A-Za-z_]+\b or \ba\.\w+\b if you get unusual results.

edited Mar 23 '21 at 13:34

answered Mar 23 '21 at 10:58

shrivallabha.redij

5,832
1
12
27

1

Avoid `A-z` in regex expressions. It will produce undesirable results. e.g. `^ [ ]` etc. characters may get included which you are not intending. The correct way would be to specify `A-Za-z` or `\w` – Pradeep Kumar Mar 23 '21 at 11:19
1

@PradeepKumar thank you for suggesting these. – shrivallabha.redij Mar 23 '21 at 13:35

T.M. · Answer 3 · 2021-03-23T14:48:31.637

Simple alternative via Filter()

Filtering of all Split() elements in a string array containing the start identification a. allows to receive already a resulting array with all wanted elements (see section a) and b)).
An eventual cosmetic action removes unnecessary characters before the identifying prefix "a." (see section c))


Function ExtractFieldnames(s As String)
Const PREFIX As String = ".a"
'a) split string into tokens
    Dim tmp() As String
    tmp = Split(s, " ")
'b) leave only elements that include fieldnames
    tmp = Filter(tmp, PREFIX, True)
'c) let them start with "a."
    Dim i As Long
    For i = 0 To UBound(tmp)
        tmp(i) = PREFIX & Split(tmp(i), PREFIX)(1)
    Next
'd) return array as function result
    ExtractFieldnames = tmp
End Function

Example call

Sub TestExtract()
    Dim s As String
    s = "'where CAST(a.DT_NPE_SORTIE as integer ) < cast (add_months (cast (a.dt_nep_restructuration as date format 'YYYYMMDD'), 12) as integer) and a.DT_NPE_SORTIE is not null and a.DT_NPE_SORTIE <> '99991231' and a.dt_npe_restructuration is not null and a.dt_npe_restructuration <> '99991231'"
    Debug.Print Join(ExtractFieldnames(s), vbNewLine)
End Sub

Results in VB Editor's immediate window

a.DT_NPE_SORTIE
a.dt_nep_restructuration
a.DT_NPE_SORTIE
a.DT_NPE_SORTIE
a.dt_npe_restructuration
a.dt_npe_restructuration

VBA extract all fieldname within a string

3 Answers3