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:
How to enter the formula as an array formula
- Select cell D1
- Paste array formula in formula bar
- Press and hold Ctrl+ Shift
- Press Enter
Download excel sample file for this tutorial:
extract_email_2.xls
(Excel 97-2003 Workbook *.xls)
Recommended blog post:
Excel udf: Filter emails from an excel range
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:
Get cell addresses from several cells with array formula
Extract cell references from all cell ranges populated with values in a sheet
Copy each sheet in active workbook to new workbooks
Increase sheet readability in excel
Extract numbers and text from a range using array formula in excel
Recommended categories
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)))
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.
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))
Rick Rothstein (MVP - Excel),
Great formula, you got me thinking there for a while! Thanks for sharing!
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.
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.
Rick your formula works!!! so thankful to you!
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?
No, I don´t think it is possible. But I believe you can use vba to search the html found at each http address.
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.
Hi Terry! I came across this post by you. I'm just trying to figure out how to get email addresses out from a bunch of all other texts in excel but I'm really unfamiliar with it. Could you give me a more detailed step wise instruction on how to do it? Thanks for your help in advance!
Jun
Terry Wright,
I tried your method and it worked great!
Thanks for commenting!
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?
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.
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)
magneticone,
See this post: http://www.get-digital-help.com/2010/08/20/filter-emails-spread-over-several-columns-in-excel/
Guys, just download ASAP utilities, it can do what you talk about and much more, and it's free! (shareware if you are a business only)
Philippe
Hey Rick - You just saved me hours of manual work...hats off to you. Thanks..
Excellent formula by Rick and Oscar
The only issue I found was that if the cell has two email addresses, it picks the first one.
Vikas,
I think you´ll find this post interesting:
Excel udf: Filter emails from an excel range
i need help with filtering emails from my list it does not have blank space with the emails nor is it in a column by itself, how can i filter out just the email.
sample: 1090245911,null,40912,22,LA,estheraguilera15@yahoo.com,null,null
mark edwards,
Read Rick Rothstein (MVP - Excel) comment in this post:
http://www.get-digital-help.com/2011/03/16/excel-udf-filter-emails-from-an-excel-range/
Thanks Rick Rothstein. That helped!
Hi,
Im using Excel 2010 and trying to pull just the emails from thousands of cells some of which range from A:L I have tried the above Formulas but keep getting #Value errors. Ive tried troubleshooting where the #error occurs but I cant seem to work it out.
Here is an example of a single Cell that im working with..
7; "John"; "Citizen"; NULL; "Br0nc0s1"; "1990"; "1986"; "1987"; "1988"; "1989"; "1990"; "john.citizen@examplemail.com"; "[Statement not provided]"; "1"
Id like for the result of the formula to be
john.citizen@examplemail.com
Let me know how you go, if I work it out myself before someone posts a result I will post the formula for refrence sake..
Thanks in advance
Tom,
Enter the formula as an array formula:
1. Select cell D1
2. Paste array formula in formula bar
3. Press and hold Ctrl + Shift
4. Press Enter
that just duplicated the cells rather then extract the email address alone..
I just tried in D1:
=INDEX(A1:C1, 1, MIN(IF(ISERROR(SEARCH("@", A1:C1)), "", COLUMN(A1:C1))))
Ctrl + Shift
while holding ctrl and shift i pressed enter.
Tom,
Take a look at the excel file attached to this post.
same thing happens even if i put the field into the excel file attached to this post. just duplicates the fields..
can you copy paste my example colomns into a spreadsheet of your own, see what I mean and if you know what needs to be done for the desired result, share that with the post? because nothing here is working for me..
Tom,
extract_email_2_tom.xls
Thanks Oscar.
So simple once you figure out you have to do the "text to column" feature first, it also can be used to remove the quotation marks! hooray, finally done with this. thanks