Extract characters from string that only match regex pattern

Question

I am working on a macro to automate the reformatting of spreadsheet data into an import sheet that can be uploaded into an ad server.

I'm importing placements which have strict naming conventions, allowing only the characters below:

a-z A-Z 0-9 _ + / \ - ( ) . : « » ~

I want to look at a string that represents the placement name and replace any character that is not found above.

So if my placement name is:

apples?orange's*pears(300x250)

I want the result to look like:

applesorangespears(300x250)

Here is what I have currently:

 'test string
 placement_name = "test*123?test'456(300x250)"

'Allowed characters
regex_pattern = "a-zA-Z0-9_+/\\-().:«»~"

 If regex_pattern <> "" Then
    With regex
        .Global = True
        .Pattern = regex_pattern
    End With

    'Execute RegEx pattern on placement name
    Set regex_matches = regex.Execute(placement_name)

    If regex_matches.count > 0 Then
        For i = 0 To regex_matches.count - 1
            placement_name = regex_matches.Item(i).Value
        Next i
    End If
End If

But when I run this it returns the full placement name, and doesn't remove characters that were not found in the regex.

Can someone provide any guidance to help me here? I can work with pseudocode or even verbal suggestions.

Thank you!

Use the `.Replace` method with `[\w+/\\().:«»~-]+` pattern and `""` (empty string) replacement. — Wiktor Stribiżew, Nov 28 '19 at 01:41
Do I need the square brackets including the +? e.g. (regex_pattern = "[\w+/\\().:«»~-]+")? Also what happened to the letters/numbers? — Kamui, Nov 28 '19 at 01:45

score 3 · Accepted Answer · answered Nov 28 '19 at 02:15

3

Dim RegEx As Object
sStr = "apples?orange's*pears(300x250)"

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Global = True

RegEx.Pattern = "[^a-zA-Z0-9-\\().:«»~-]"

Debug.Print RegEx.replace(sStr, "")

You can change your RegEx pattern to suit in case if I missed something.

answered Nov 28 '19 at 02:15

Michal Rosa

2,320
1
11
19

1

This is simpler than I thought and works! Thank you!! – Kamui Nov 28 '19 at 02:23

Extract characters from string that only match regex pattern

1 Answers1