How to extract email addresses from a excel sheet
Question: How to extract email addresses from this sheet? (See pic below)
Answer:
Array formula in D2:
=INDEX(A1:C1, 1, MIN(IF(ISERROR(SEARCH("@", A1:C1)), "", COLUMN(A1:C1)))) + CTRL + SHIFT + ENTER copied down as far as necessary.
Download excel sample file for this tutorial:
extract_email_2.xls
(Excel 97-2003 Workbook *.xls)
External resources
Functions
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
ROW(reference) returns the rownumber of a reference
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE
COLUMN(reference) Returns the column number of a reference
SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)
Related posts:
- Filter duplicate values in a range using “contain” condition in excel
- Filter unique distinct text values in a range using “contain” condition in excel
- Filter unique text values in a range using “contain” condition in excel
- Extract duplicate text values from a range containing both numerical and text values in excel
November 4th, 2009 at 8:44 pm
Here's something I came up with to extract the email from an adjacent cell (called A1) of text(so put this formula in B1 and copy down). It assumes the email address has a blank space on either side within the body text.
=CONCATENATE(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("@",A1,1)-1)," "," ^!",(LEN(LEFT(A1,SEARCH("@",A1,1)-1))-LEN(SUBSTITUTE(LEFT(A1,SEARCH("@",A1,1)-1)," ","")))),(SEARCH("@",A1,1)-SEARCH("^!",SUBSTITUTE(LEFT(A1,SEARCH("@",A1,1)-1)," "," ^!",(LEN(LEFT(A1,SEARCH("@",A1,1)-1))-LEN(SUBSTITUTE(LEFT(A1,SEARCH("@",A1,1)-1)," ","")))),1))),MID(A1,SEARCH("@",A1,1),SEARCH(" ",A1,SEARCH("@",A1,1))-SEARCH("@",A1,1)))
November 4th, 2009 at 9:33 pm
Fred,
Your formula works! Thanks for sharing!
Thanks also for bringing this post to my attention. I have changed the array formula and the attached excel file.
November 6th, 2009 at 3:39 pm
Here is a much shorter formula to find the email address within a cell and it allows that email address to be anywhere within the text (beginning, middle or end)...
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99))&MID(A1,FIND("@",A1),FIND(" ",A1&" ",FIND("@",A1))-FIND("@",A1))
November 7th, 2009 at 11:53 pm
Rick Rothstein (MVP - Excel),
Great formula, you got me thinking there for a while! Thanks for sharing!
November 8th, 2009 at 10:21 am
I'm glad you were able to figure out how my formula (for finding an email address inside a text string) works. Here is a slightly different formula that does what you posted in your original blog entry (get the email address from whatever column it is in)...
=INDEX(A1:C1,1,MIN(IF(ISERR(SEARCH("@",A1:C1)),"",COLUMN(A1:C1))))
This formula, like the one you blogged, requires Ctrl+Shift+Enter to commit it.
November 8th, 2009 at 3:42 pm
Whoops! Sorry, I posted your blogged formula by mistake; here is the formula I meant to post...
=INDEX(1:1,,MAX(COLUMN(A1:C1)*NOT(ISERROR(SEARCH("@",A1:C1)))))
And again, for completeness sake, this formula, like the one you blogged, requires Ctrl+Shift+Enter to commit it.
February 9th, 2010 at 9:19 pm
Rick your formula works!!! so thankful to you!
April 14th, 2010 at 2:57 pm
I have a excel-document with some 2000 rows, with link to contacts details on the internet (where I can find e-mailaddresses) Is it possible to make a string that works for that?
April 15th, 2010 at 9:41 pm
No, I don´t think it is possible. But I believe you can use vba to search the html found at each http address.
May 3rd, 2010 at 9:10 pm
A very quick method to extract email addresses is to use a text filter. Select the column with email addresses (and other info) and apply a text filter, using the 'contains' parameter, to filter on the '@' character. That will collapse all rows without an email address, allowing you to cut and past only the email addresses to another spreadsheet, or elsewhere.
May 4th, 2010 at 8:43 pm
Terry Wright,
I tried your method and it worked great!
Thanks for commenting!
August 17th, 2010 at 10:13 pm
Any idea how to adapt the formula to do a conditional (i.e. comma-separated) merge of the data from multiple columns, in the case that a single row has emails spread over several columns?
August 19th, 2010 at 1:37 pm
I think I would need to change the MIN as it only pulls the first match out of the set of matched SEARCH values (i.e. anything containing an "@" symbol)... if anyone has an idea of a better function to use than MIN please let me know... my first inclination was to use CONCAT but didn't seem to work.
August 19th, 2010 at 1:44 pm
Well for starters it is CONCATENATE not CONCAT, but this thing is driving me nuts.... beer $ for anyone who solves it
Here's what I'm trying to do:
some1@email.com | some2@email.com | some3@email.com | CONCATENATED LIST
The formula posted here only takes the first occurrence of an email in any column, I'd like to merge a comma-separated list of all the emails in separate columns (same row)
August 20th, 2010 at 1:48 pm
magneticone,
See this post: http://www.get-digital-help.com/2010/08/20/filter-emails-spread-over-several-columns-in-excel/