CODEWITHSUNDEEP
regexvba
moonweazel
moonweazel

Reputation: 79

VBA regex to remove all but the email addresses

I am trying to clean up a string of email addresses:

[email protected];#30;#[email protected];#32;#[email protected];#33

I don't know how many emails will be in the list and the numbers will change length too.

I have tried:

(?!([\w-]+\.)[\w-]+@([\w-]+\.)+[\w;]+)

as a pattern but it doesn't work.

This is all being done in VBA.

Upvotes: 1

Views: 213

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

You don't need a regex to do this.

Your addresses are delimited with a ",#" string. Use the Split function to split your string into an array of values. Then iterate the array and only bother with entries that contain a @ character.

Below code produces this output:

[email protected]
[email protected]
[email protected]

Sub Test()
    Const stringValues As String = "[email protected];#30;#[email protected];#32;#[email protected];#33"
    Dim values As Variant
    values = Split(stringValues, ";#")

    Dim value As Variant
    For Each value In values
        If InStr(value, "@") Then
            ' You've got mail!
            Debug.Print value
        End If
    Next
End Sub

Upvotes: 3

Scott Weaver
Scott Weaver

Reputation: 7361

"I need to flip it around so the email addresses remain and the numbers and hash signs are removed"

you could target the stuff you want to remove instead of targeting the valid parts:

#\d+;?#?

"a pound sign, followed by one or more numbers, followed by a semicolon (maybe), followed by a pound sign (maybe)"

now simply do a replace with empty string and you should have a clean list of email addresses.

Upvotes: 2

Jan
Jan

Reputation: 43169

Depending on your real input strings, you could come up with sth. like:

([-.\w]+@[-.\w]+);

See a demo on regex101.com. "Remove all but" comes down to extracting what you want.

Upvotes: 0

Related Questions