Extracting email from a string

  • Thread starter Thread starter MPoirier
  • Start date Start date
M

MPoirier

I want to extract an E-Mail from a text string in Excel :

"This is an automatically generated Delivery Status Notification.
Delivery failed. (e-mail address removed). Please try again."

The E-Mail is not always the Nth word of the string.



------------------------------------------------
Message posted

-- View and post Excel related usenet messages directly from http://www.ExcelForum.com
at http://www.ExcelTip.com/
------------------------------------------------
 
I really hesitate to give the solution without knowing
whether you're using it to enhance some sort of spam
generation routine....
-----Original Message-----
I want to extract an E-Mail from a text string in Excel :

"This is an automatically generated Delivery Status Notification.
Delivery failed. (e-mail address removed). Please try again."

The E-Mail is not always the Nth word of the string.
Click to expand...
from http://www.ExcelForum.comat http://www.ExcelTip.com/
 
Try something like the following. This assumes that the @
character appears only in the email address.


Sub AAA()
Dim S As String
Dim Res As String
Dim Pos1 As Integer
Dim Pos2 As Integer
S = "this is some text (e-mail address removed) more text"
Pos2 = InStr(1, S, "@")
Pos2 = InStr(Pos2, S, " ") - 1
Pos1 = InStrRev(S, " ", Pos2 - 1) + 1
Res = Mid(S, Pos1, Pos2 - Pos1 + 1)
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
pretty busy but it works:
if string is in A1, use:
=TRIM(MID(SUBSTITUTE(MID(A1,FIND("@",A1)-
20,41),". "," "),20,51))

According to what you say, though, the email WOULD be at
the Nth position ALL the time. The @ sign probably would
not be, however. If that's the case, there would be many
other string manipulations that would accomplish this.

-----Original Message-----
I want to extract an E-Mail from a text string in Excel :

"This is an automatically generated Delivery Status Notification.
Delivery failed. (e-mail address removed). Please try again."

The E-Mail is not always the Nth word of the string.
Click to expand...
from http://www.ExcelForum.comat http://www.ExcelTip.com/
 
Back
Top