Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 to contact us...... OR Schedule an appointment for assistance, or email with your questions... Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract emails from cells with text
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" wrote in message ... 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 to contact us...... OR Schedule an appointment for assistance, or email with your questions... Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract emails from cells with text
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 "AJexcelQuestions" wrote in message ... 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 to contact us...... OR Schedule an appointment for assistance, or email with your questions... Thank you |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract emails from cells with text
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" wrote in message ... 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 to contact us...... OR Schedule an appointment for assistance, or email with your questions... Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract text on right of various length from cells | Excel Discussion (Misc queries) | |||
Extract data from emails | Excel Worksheet Functions | |||
How to extract just text from cells | Excel Worksheet Functions | |||
how do I create email list from individual emails in Excel cells? | Excel Discussion (Misc queries) | |||
sending emails from adresses in cells? | Excel Discussion (Misc queries) |