# Extract from string

#### AndyD

##### Active Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Hi Andy

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

Code:
``````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:

Code:
``=GetEmail(A1)``

for example.

Best regards

Richard

Here's a non-regular expression option:

Code:
``````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

Thanks

Any other ways of doing apart from UDF?

A

Hi,

Try:

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

That's a really slick formula Fairwinds

Remarkably simple too.

Richard

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.

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)?

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.

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.

Replies
9
Views
105
Replies
2
Views
173
Replies
6
Views
107
Replies
1
Views
74
Replies
4
Views
149

1,220,987
Messages
6,157,230
Members
451,407
Latest member
vdaesety

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back