Extract from string

AndyD

Active Member
Joined
Nov 14, 2002
Messages
449
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Hi,

Try:

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

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

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

New Member
Joined
Aug 28, 2007
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,263
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 28, 2007
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,420
Messages
5,675,751
Members
419,585
Latest member
popsin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top