Code to extract E-Mail addresses

[Dazzawm]

I have thousands of rows with email addresses in column A. Problem is they are amongst other text at the beginning, middle or end. Can a code be made please to extract the email addresses in to column B next to it? I haven't posted an example as for security and its pretty self explanatory.


Thanks if you can help.

 

[Dazzawm]

Kindly don't promote the use of off-site file hosting. Such links are prone to failure in the longer term. Instead, users should employ the posting aids provided by this site, which is usually quite sufficient. See: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
Off-site file hosting should be countenanced as a last resort only, not as the first resort.

I thought when I saw you replied I had a solution!!

 

[steve the fish]

I dont know much about regex but ive cobbled together this from stuff i just looked up on the net. Works ok it seems:

Function ExtractEmail(ByVal SearchString As String) As String

With CreateObject("vbscript.regexp")
    .Pattern = "([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)"
    .Global = True
    .IgnoreCase = True
    Set em = .Execute(SearchString)
End With

ExtractEmail = em(0).Value

End Function

 

[Dazzawm]

For some reason the macro button on menu bar is greyed out. Any ideas?

 

[steve the fish]

You can use it on a worksheet. =ExtractEmail(A1)

 

[Dazzawm]

But I need to insert it into a module and I cant get into VBA

 

[Dazzawm]

I dont know much about regex but ive cobbled together this from stuff i just looked up on the net. Works ok it seems:

Function ExtractEmail(ByVal SearchString As String) As String

With CreateObject("vbscript.regexp")
    .Pattern = "([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)"
    .Global = True
    .IgnoreCase = True
    Set em = .Execute(SearchString)
End With

ExtractEmail = em(0).Value

End Function

Worked great thanks.

 

[jtakw]

Hi,

Formula option, give this a try:

Book1
	A	B
1	Darrens twitter is [MENTION=97701]Example[/MENTION] and his email is [email]example@gmail.com[/email]	example@gmail.com
2	Can you tell me whos email [email]example@hotmail.co.uk[/email] is please	example@hotmail.co.uk
Sheet472

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),FIND("@",SUBSTITUTE(SUBSTITUTE(A1," @"," |")," ",REPT(" ",100)))-100,200))