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:
- Sum text cells using criteria in excel
- Search and display all cells that contain all search strings in excel
- Filter duplicate values in a range using “contain” condition in excel
- Filter unique distinct text values in a range using “contain” condition in excel
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- 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
- Search for multiple text strings in multiple cells in excel, part 2
- Lookup with multiple criteria and display multiple search results using excel formula
- Search for a text string in an excel table
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!