How to extract multiple substrings from an expression using VBA Excel

Question

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.

I couldn't try anything since I am new to VBA. Only C and Java are my forte. — Akanksha Goel
– Akanksha Goel, Commented Jan 21, 2015 at 9:23

Pᴇʜ · Accepted Answer · 2015-01-20 07:44:25Z

3

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

answered Jan 20, 2015 at 7:44

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Akanksha Goel Over a year ago

Cool.. Its been really helpful. I have one more question if we can replace heading1, heading2, heading3 and so on in the expression with #1, #2, ...and so on, with consecutive numbers. Can you help me out which function to use for the above?

Pᴇʜ Over a year ago

You can adjust the pattern in between the brackets [a-zA-Z0-9]. At the moment it allows any lower case and upper case letters as well as numbers. If you need other characters you have to modify that pattern. E.g. to allow # change it to [a-zA-Z0-9#]. But be careful some special characters have to be escaped with a backslash as they are used as operators in regular expressions.

Collectives™ on Stack Overflow

How to extract multiple substrings from an expression using VBA Excel

1 Answer 1

Example

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Example

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related