Extract emails from cells with text

I have a row in column A which includes an email address in the text that I'd 
like to extract to column B.  Is there a formula I can use to accomplish 
extracting the email address only to column B?

Here's an example of different cells in column A:

Please email example@law.ufl.edu to contact us......
OR 
Schedule an appointment for assistance, or email example@uga.edu with your 
questions...

Thank you
0
Utf
2/3/2010 11:13:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
2030 Views

Similar Articles

[PageSpeed] 2

Try this...

All on one line:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND
(" ",A1&" ",FIND("@",A1))-1)," ",
REPT(" ",LEN(A1))),LEN(A1)))

-- 
Biff
Microsoft Excel MVP


"AJexcelQuestions" <AJexcelQuestions@discussions.microsoft.com> wrote in 
message news:07688F94-DA78-422D-9CCC-C2925A9B82C5@microsoft.com...
>I have a row in column A which includes an email address in the text that 
>I'd
> like to extract to column B.  Is there a formula I can use to accomplish
> extracting the email address only to column B?
>
> Here's an example of different cells in column A:
>
> Please email example@law.ufl.edu to contact us......
> OR
> Schedule an appointment for assistance, or email example@uga.edu with your
> questions...
>
> Thank you 


0
T
2/3/2010 11:24:45 PM
Sub getemailinstr()
Set mc = Range("f4")
findat = InStr(mc, "@")
'MsgBox findat
st = InStrRev(mc, " ", findat)
'MsgBox st
es = InStr(findat, mc, " ")
'MsgBox es
mc.offset(,1).value=Mid(mc, st, es - st)
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"AJexcelQuestions" <AJexcelQuestions@discussions.microsoft.com> wrote in 
message news:07688F94-DA78-422D-9CCC-C2925A9B82C5@microsoft.com...
>I have a row in column A which includes an email address in the text that 
>I'd
> like to extract to column B.  Is there a formula I can use to accomplish
> extracting the email address only to column B?
>
> Here's an example of different cells in column A:
>
> Please email example@law.ufl.edu to contact us......
> OR
> Schedule an appointment for assistance, or email example@uga.edu with your
> questions...
>
> Thank you 

0
Don
2/3/2010 11:38:23 PM
On Wed, 3 Feb 2010 15:13:02 -0800, AJexcelQuestions
<AJexcelQuestions@discussions.microsoft.com> wrote:

>I have a row in column A which includes an email address in the text that I'd 
>like to extract to column B.  Is there a formula I can use to accomplish 
>extracting the email address only to column B?
>
>Here's an example of different cells in column A:
>
>Please email example@law.ufl.edu to contact us......
>OR 
>Schedule an appointment for assistance, or email example@uga.edu with your 
>questions...
>
>Thank you

One way is with a user defined function (UDF).

 To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like 

	=ExtractEmail(A1)

in some cell.

This UDF makes the assumption that the email address is defined by being a
single substring that contains a "@".  If that is not sufficient, a more
detailed specification can be devised.

=============================================
Option Explicit
Function ExtractEmail(s As String) As String
 Dim re As Object, mc As Object
 Const sPat As String = "\b\S+@\S+\b"
Set re = CreateObject("vbscript.regexp")
    re.Pattern = sPat
If re.test(s) = True Then
    Set mc = re.Execute(s)
    ExtractEmail = mc(0)
End If
End Function
====================================
--ron
0
Ron
2/4/2010 12:23:01 AM
Here is another UDF for you to consider...

Function ExtractEmail(S As String) As String
  Dim Parts() As String
  Parts = Split(S, "@")
  ExtractEmail = Split(Parts(1))(0)
  Parts = Split(Parts(0))
  ExtractEmail = Parts(UBound(Parts)) & "@" & ExtractEmail
End Function

-- 
Rick (MVP - Excel)


"AJexcelQuestions" <AJexcelQuestions@discussions.microsoft.com> wrote in 
message news:07688F94-DA78-422D-9CCC-C2925A9B82C5@microsoft.com...
>I have a row in column A which includes an email address in the text that 
>I'd
> like to extract to column B.  Is there a formula I can use to accomplish
> extracting the email address only to column B?
>
> Here's an example of different cells in column A:
>
> Please email example@law.ufl.edu to contact us......
> OR
> Schedule an appointment for assistance, or email example@uga.edu with your
> questions...
>
> Thank you 

0
Rick
2/4/2010 3:47:20 AM
Reply:

Similar Artilces:

finding old emails
i can't seem to find all my old emails in my in box is ther a way to track them down? thanks peter If you go to View | Current View (or View | Arrange By | Current View if you're using Outlook 2003), is it set to Messages? If so, you should be seeing all of your messages, as there is no filter set by default on the Messages view. If you added a filter, you can remove it by resetting the view. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:ets%236Xa...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Case Email Template
It seems that I am encountering a problem with data fields displaying the wrong information in my outgoing email when using an email template. It seems as if the customer information (customer/contact) is being displayed in place of the user/owner information. For example, a customer (Rep1) issues a ticket and the owner of the ticket, Kate, is assigned. In the outgoing email to the customer stating that an case has been assigned and to whom it is assigned is displaying the customer information instead of the owner. Saying to contact Kate and giving Kate's email when I state t...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

hyperlinks & cell reference
Hi folks, Everytime I insert a new column into my Excel table the hyperlinks which are linked to the cells don't change automaticlly. Thus I've to change every following hyperlink per hand. Is there an option to relate hyperlinks to an other reference point than the cells? Thank you for your time in advance Joerg If I gave the range a nice name, I could use that in my hyperlink: Using the =hyperlink() worksheet formula: =HYPERLINK("#testname","your friendly name here") TestName referred to c9. Inserting a new column and now it referred to D9. Joerg Ce...

Leave Zero's in cell
Hi, when I put three zero's into a cell,.excel automatically changes it back to one zero. I want to keep the three zero's in the cell. Woiuld appreciate any advice please Regards Ivan Hi Ivan Either: Format the cell as Text (although then the numbers entered are text rather than genuine numerics) or format the cells to show leading zeroes (eg a custom format like 000) - note that this is simply for display purposes as the actual contents of the cell will be 0. Hope this helps1 Richard On 15 Mar, 10:47, "Ivan B" <nos...@nospam.net> wrote: > Hi, > when...

Formula cells needs to display "zero"
How do i get a cell with a formula in it to display a zero until a figure is typed into a corresponding cell? All productive help is greatly appreciated. -- thank you Assuming your formula is SUM(B1:B20), try: =IF(A1="",0,SUM(B1:B20)) Regards, KL "hello" <hello@discussions.microsoft.com> wrote in message news:6716AFD9-2CE5-4E56-9863-45760F652082@microsoft.com... > How do i get a cell with a formula in it to display a zero until a figure > is > typed into a corresponding cell? > All productive help is greatly appreciated. > -- > thank you ...

Adding email addresses to contacts contained within an email message
This is a multi-part message in MIME format. ------=_NextPart_000_000E_01C3F079.723AEFC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Does anyone know of either a macro or utility that would update all = email recipients contained in the body of an email message to the = contacts? TIA, Alan ------=_NextPart_000_000E_01C3F079.723AEFC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD>...

problems creating a archive file to archive old emails
Hello everybody I have enable the option to archive old emails to clean up the inbox the "big deal" is that before it I change the file name from archive6.pst (default) to outlook_archive.pst and I run partially the process. And now when I run the archive option start creating me a archive6 file (when half of the archiving is already done in the other file) I notice that they are two places where to maintain the archive file name (?) :: File -> Archive -> Archive file Tools -> options -> other -> AutoArchive -> Defaulst folder settings for archiving ->...

Outlook 2003 Email Select Names Type name or select from list
New Message: When I try to use the Select Names - Type name or select from list in an Email New Message, the search is not as expected. For example, I enter the Name Smith, and the selection will be another name starting with S, but not Smith. This Address was from Outlook Express and worked fine there. Thank you. Please describe more completely what you are doing. Perhaps you are using autocompletion instead of autoresolution. You didn't provide enough details for us to tell. -- Russ Valentine [MVP-Outlook] "nsnews.microsoft.com" <nfn02958@naples.net> wrote in message...

Email account question
I am new to Outlook having recently switched my email from AOL to Outlook 2003. I have set up a primary and 3 secondary email addresses, but all of the incoming emails are all going to the same inbox. Don't each of the email addresses have their own mailbox? It seems odd that everyones emails should all be coming to the same box. Is this the way Outlook works, or am I doing something wrong? Have looked all over the "help" section but can find no answers. Any help would be appreciated. Thanks, RC. You can use rules or Search Folders to move messages to separate folders. --...

verifying microsoft email accound
how do I get into my microsoft email account to verify it ...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Creating HTML email in Outlook 2007
Hi! I'm using Outlook 2007 on XP SP3 and have suddenly run into a change in the way Outlook seems to work...or at least in the way that I like to use Outlook. Normally all my outgoing emails are in plain text but every now and again I need to send out an HTML email. I'll create the copy in Notepad with appropriate HTML coding and save it as a .htm file. I'll open up a new message, change the format to HTML and then insert the file as text. Up until now that system has worked just fine...until today. I went throught the same procedure and when I went to add file as ...

secure email setup (digital signatures)
Can someone point me in the right direction for instructions on setting up secure email in Outlook 2000? I need to setup certificates so that email can be digitally signed by some of my users and so that these users can verify authenticity of the senders of some of their emails. any input would be greatly appreciated. thanks. Will your site be acquiring certificates from an online authority like Verisign/Thawte or going with an internal certificate authority? The later introduces issues of its own since the public will not know about your internal certificate authority. In any event, ...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

How to get cell coordinates?
As previously posted, I am trying to build a formula that updates its cell references automatically based on what line the formula is entered. My formula will be more complex but for the sake of simplicity, here's an example of what I'm trying to achieve... A1 = 100 A2 = 25 A3 = C3 B1 = 50 B2 = 10 B3 = C3 C3 = formula that adds A1+A2 or B1+B2, depending on the row number. Please remember that this is a simple example and that my actual formula will be more complex and therefore, an IF statement will be too long for what I want to achieve. In other words, my formu...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Email Subject GUID to Email Header
I have the understanding that there is a patch which, rather than removing the GUID from the email completely, move the GUID from the subject line to the email header. After scouring the microsoft site and being unable to find this anywhere, I should be very grateful if someone would point me in the right direction and help to convince me that I've not made this up... or worse dreamt it... Thankyou in advance ...

Text running
Hello I don't know if there is a code that keeps a word ( for example FOOTBALL) running from left to the right of the cell. I appriciate even a small help. Thanks in advance Where would you like it to run to? English and most other languages run left to right. Middle East countries usually run right to left. There are various methods of text alignment in cell formatting>alignment. Gord Dibben MS Excel MVP On Thu, 15 Apr 2010 09:39:01 -0700, MAX <MAX@discussions.microsoft.com> wrote: >Hello >I don't know if there is a code that keeps a wor...

AutoSave Email on Reply?
Anyone know how to auto save an email address to your address book when you reply to it? Outlook 2000 had it but I can't find it in 2002? Thanks! Eric This options only was in Outlook 2000 installed in IMO mode. For all other versions you'll need an Add-in from Sperry Software; http://www.sperrysoftware.com/Outlook/Add-Email-Address.asp?Source=RS If you decide to order use "BH93RF24" to get a discount -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tip of the month: -Create your o...

Unresponsive email
I can open my Outlook & see my emails. But the emails won't open and I get message Outlook not responding? How can I correct this? ...

I can not email excel files
I am using xp- I can"t email a excel file? newbe wrote: || I am using xp- I can"t email a excel file? Umm what happens when you try? -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk And what are you doing to try?? -- Don Guillett SalesAid Software donaldb@281.com "newbe" <newbe@discussions.microsoft.com> wrote in message news:4EBA3AC3-F379-4973-AB64-FBA9FD7F43A5@microsoft.com... > I am using xp- I can"t email a excel file? i was trying to e mail right out of excel but it would not work. I found out...

How to insert an email link that makes a calendar entry?
Say, I'm sending an email that notifies the recepient about the topic, date, and time of an upcomming event. How do I insert a link into the email that when the recepient clicks on the link, then automatically the topic is entered into the recipient's calendar at the correct date and time? Save the event in the ics or vcs format via "Save As". Place the file on a web server and link to the file in the message like you would do to any other website. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ ...