Extract from string

[AndyD]

hi
I have a (notes) field that I've queried into excel which contains (amongst other text) an email address.

As email addresses contain "@" can I extract these from the rest of the text?

Cheers

Andy

 

[Richard Schollar]

Hi Andy

This UDF uses a RegEx I got from http://www.regular-expressions.info/:

Function GetEmail(txt As String) As String
Dim matches As Object
With CreateObject("VBScript.RegExp")
    .Pattern = "\b[a-zA-Z0-9._%-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}\b"
    .Global = True
    Set matches = .Execute(txt)
    GetEmail = ""
    If matches.Count Then GetEmail = matches(0)
End With

Use in Excel like a normail formula:

=GetEmail(A1)

for example.

Best regards

Richard

 

[giacomo]

Here's a non-regular expression option:

Public Function getEmail(note As String) As String
Dim x As Variant
Dim i As Integer

x = Split(note, " ")

For i = 0 To UBound(x)
    If x(i) Like "*@*.*" Then
        getEmail = x(i)
        Exit Function
    End If
Next i

End Function

hth,
Giacomo

 

[AndyD]

Thanks

Any other ways of doing apart from UDF?

A

 

[fairwinds]

Hi,

Try:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",20)),FIND("@",SUBSTITUTE(A1," ",REPT(" ",20)))-20,40))

 

[Richard Schollar]

That's a really slick formula Fairwinds

Remarkably simple too.

Richard

 

[fairwinds]

Thanks.

Actually should be:

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",20)),FIND("@",SUBSTITUTE(" "&A1," ",REPT(" ",20)))-20,40))

to handle if string starts with the adress.

 

[test_man2]

I have a question for fairwinds - what would you do if the field has more than one email address in it?
Such as - Update abcdefgh@gmail.com to qrstuvwx@att.net.

The current formula picks up the first address but not the second. How could you put the second address into the next column (my guess is the easiest/best way is to have a formula in the next column)?

 

[Joe4]

I have a question for fairwinds

This post is almost 15 years old and Fairwinds has not been on this site in over a year!
And since your question is signficantly different than the original question, your best bet would be to start a brand new thread for your question.

 

[test_man2]

Thanks for the reply, Joe. Reading the instructions at the bottom regarding posting a new question or asking a question, I thought it was required that I post the question here since it is relevant to the solution. I'll post a new question.