CODEWITHSUNDEEP
arraysstringvba
Akanksha Goel
Akanksha Goel

Reputation: 41

How to extract multiple substrings from an expression using VBA Excel

I have an expression input from a user which can be like

{{heading1} + {{heading2} * {heading3}}}

Each heading is included in braces {} when written in expression.

I want to extract "heading1" "heading2" and "heading3" in a String Array separately using an Excel macros. Please help me with the code.

Upvotes: 0

Views: 1178

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

You can use regular expressions. Regular expressions are used for Pattern Matching. To use in Excel follow these steps.

Step 1: Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

  • Select "Developer" tab (I don't have this tab what do I do?)
  • Select "Visual Basic" icon from 'Code' ribbon section
  • In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
  • Select "References"
  • Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
  • Click "OK"

Example

Private Sub regEx()

    Dim UserInput As String
    UserInput = "{{heading1} + {{heading2} * {heading3}}}"

    Dim Pattern As String
    Pattern = "\{([a-zA-Z0-9]*)\}"

    Dim regEx As New RegExp

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = Pattern
    End With

    Dim Matches
    Set Matches = regEx.Execute(UserInput)

    For Each Match In Matches
        Debug.Print "Match found at position " & Match.FirstIndex
        Debug.Print "Match Value is '" & Match.Value & "'"
        Debug.Print ""
    Next

End Sub

Upvotes: 3

Related Questions