Extract email address from text

[Brianfree]

Hi, i am trying to extract email addresses from my spam lists...

each line of the file looks something like...

Detected junk message from Sam Hwa Tech <samhwatech@yahoo.co.kr> - 전달: Hope to do business with your company. (related to MACS show) at 02/03/2009 09:02:05

How can i extract the email address so it looks like...

<samhwatech@yahoo.co.kr>

Many thanks,

Brian

 

[HarleyQuinn]

If they are always enclosed in <..> then the following user defined function should work:

Function ReturnEmail(strValue As String) As String

Dim re As Object
Dim mc As Object

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "<.*?>"
    Set mc = .Execute(strValue)
End With

If mc.Count > 0 Then
    ReturnEmail = mc.Item(0).Value
Else
    ReturnEmail = ""
End If

Set mc = Nothing
Set re = Nothing

End Function

You would call this function in the following example way:

=returnemail(A1)

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[TonyJollans]

Where have you got the list? In Word?

If each line in your list ends in a paragraph mark ...

Ctrl+H (to invoke Find and Replace)
Replace [blue][tt]*(\<*\>)*^13[/tt][/blue]
With [blue][tt]\1^p[/tt][/blue]
Press "Replace All"

If each line ends in a manual line break, then use:
Replace [blue][tt]*(\<*\>)*^11[/tt][/blue]
With [blue][tt]\1^l[/tt][/blue] (that's backslash, one, caret, letter ell)

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[kjv1611]

Tony, out of curiosity, I gave your trick a try, but it did not work..

I simply copied and pasted the example text from the OP into a Word document and a Text document (each blank before pasting), and tried the Replace option each, but no go...

Unless I'm missign something, something is ary in that method.. or else I somehow did a simple Find and Replace wrong!

--

"If to err is human, then I must be some kind of human!" -Me

 

[fumei]

It does not work for me, either.

I can not even get \<*\> to work.

\< finds the <
\> finds the >

but putting the * between them does not find anything. I feel really dumb, but them I am having a VERY stupid day.

BTW: I tried with, and without, wildcards checked.

However.....

\<*\> does work (find) all instances between < and >, as in:

<yadda>

askdhakdhakdha <blah blah> whatever <more crap>

Could it be the @ in: samhwatech@yahoo.co.kr???

Gerry

 

[TonyJollans]

Sorry

You must check "Use wildcards". You do also need the trailing delimiter (so Gerry's example won't work) and you must do the replace or the second find will be a substring of the first.

I just checked in 2003 to be sure and it works for me - but, and I don't know why, it doesn't find a string terminating in the final paragraph mark in the document (which may be what caused kjv's test to fail) - it does find it in 2007, but not in 2003.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

OK, but why does:

\<*\>

find ONLY the highlight text in the following (Word 2002)?

Blah blah [highlight]<yadda>[/highlight] whatever this and [highlight]<that>[/highlight] will be forever in your [highlight]<deb@t>[/highlight]
Yadda blah <samhwatech@yahoo.co.kr>

It does NOT find <samhwatech@yahoo.co.kr>.

Wildcards checked.

I have tried with, and without, field codes toggled.

<{ HYPERLINK "mailto:samhwatech@yahoo.co.kr" }>

does not work either.


Gerry

 

[TonyJollans]

Thankyou for persevering, Gerry. I was testing without hyperlinks - because that's what I got when I pasted.

As you point out it doesn't work with hyperlinks (2002 or 2007) - I can only assume it's one of those annoying features of F&R, that we commonly call bugs.

This all makes it harder to come up with a viable non-macro solution. I'll sleep on it.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Salut39]

I used Text to Column function and in delimiters, selected Other "<", and then repeat on the result again with Other set to ">", took 30 sec.

Yuri

 

[kjv1611]

So, Salut39, did you copy/paste from Word to Excel, or how did you move the data to Excel in the first place? I'm not aware that you can use Text to Columns within Word.

--

"If to err is human, then I must be some kind of human!" -Me

 

[HarleyQuinn]

Got to say, not once has the OP come back and say where the list is actually stored...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[kjv1611]

Good point. I was just assuming it was in a text file... and someone else mentioned it could have been in a Word doc.. Then the Excel reference...

Well, Brianfree, where is it? And where are you?

--

"If to err is human, then I must be some kind of human!" -Me