## How to extract email addresses from an Excel sheet

**Question:** How to extract email addresses from this sheet?

**Answer:**

It dependsĀ on how the emails are populated in your worksheet?

- Are they in a single cell each?
- Are there other text strings in the cell as well?

## Example 1,

The following formula works if a cell contains only an email address, see image above. The TEXTJOIN function extracts all emails based on if character @ is found in the cell.

Array formula in cell C8:

Excel 365 formula in cell B9:

### Explaining formula

#### Step 1 - Rearrange array to a single column array

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

TOCOL(B2:E6)

becomes

TOCOL({"AA", "BB", "CC", "DD"; "EE", "[email protected]", "GG", "HH"; "II", "JJ", "KK", "LL"; "MM", "NN", "OO", "[email protected]"; "[email protected]", "RR", "SS", "TT"})

and returns

{"AA"; "BB"; "CC"; "DD"; "EE"; "[email protected]"; "GG"; "HH"; "II"; "JJ"; "KK"; "LL"; "MM"; "NN"; "OO"; "[email protected]"; "[email protected]"; "RR"; "SS"; "TT"}

#### Step 2 - Search for character @

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

Function syntax: SEARCH(find_text,within_text, [start_num])

SEARCH("@",TOCOL(B2:E6))

becomes

SEARCH("@", {"AA"; "BB"; "CC"; "DD"; "EE"; "[email protected]"; "GG"; "HH"; "II"; "JJ"; "KK"; "LL"; "MM"; "NN"; "OO"; "[email protected]"; "[email protected]"; "RR"; "SS"; "TT"})

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 5; 4; #VALUE!; #VALUE!; #VALUE!}

#### Step 3 - Look for numbers

The ISNUMBER function checks if a value is a number, returns TRUE or FALSE.

Function syntax: ISNUMBER(value)

ISNUMBER(SEARCH("@",TOCOL(B2:E6)))

becomes

ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 5; 4; #VALUE!; #VALUE!; #VALUE!})

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE}

#### Step 4 - Filter values based on corresponding boolean values

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(TOCOL(B2:E6),ISNUMBER(SEARCH("@",TOCOL(B2:E6))))

becomes

FILTER({"AA"; "BB"; "CC"; "DD"; "EE"; "[email protected]"; "GG"; "HH"; "II"; "JJ"; "KK"; "LL"; "MM"; "NN"; "OO"; "[email protected]"; "[email protected]"; "RR"; "SS"; "TT"}, {FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE})

and returns

{"[email protected]";"[email protected]";"[email protected]"}

## Example 2,

The example above has multiple text strings in each cell separated by a blank, the formula is only capable of extracting one email address per cell and if the delimiting character is a blank (space). You can change the formula to use any delimiting character, however, only one delimiting character per formula.

Formula in cell C3:

Excel 365 formula in cell D3:

### Explaining formula

#### Step 1Ā - Merge strings in multiple cells

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(" ",TRUE, B3:B5)

becomes

TEXTJOIN(" ",TRUE, {"DD AA [email protected]";"CC [email protected] AA";"FF GG HH [email protected]"})

and returns

"DD AA [email protected] CC [email protected] AA FF GG HH [email protected]"

#### Step 2Ā - Split strings based on a space character as a delimiting value

The TEXTSPLIT function splits a string into an array based on delimiting values.

Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(TEXTJOIN(" ",TRUE, B3:B5),," ")

becomes

TEXTSPLIT("DD AA [email protected] CC [email protected] AA FF GG HH [email protected]",," ")

and returns

{"DD"; "AA"; "[email protected]"; "CC"; "[email protected]"; "AA"; "FF"; "GG"; "HH"; "[email protected]"}.

#### Step 3Ā - Search for a @ character

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

Function syntax: SEARCH(find_text,within_text, [start_num])

SEARCH("@",TEXTSPLIT(TEXTJOIN(" ",TRUE, B3:B5),," "))

becomes

SEARCH("@",{"DD"; "AA"; "[email protected]"; "CC"; "[email protected]"; "AA"; "FF"; "GG"; "HH"; "[email protected]"})

and returns

{#VALUE!; #VALUE!; 5; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 4}.

#### Step 4Ā - Find numbers in array

The ISNUMBER function checks if a value is a number, returns TRUE or FALSE.

Function syntax: ISNUMBER(value)

ISNUMBER(SEARCH("@",TEXTSPLIT(TEXTJOIN(" ",TRUE, B3:B5),," ")))

becomes

ISNUMBER({#VALUE!; #VALUE!; 5; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 4})

and returns

{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}.

#### Step 5Ā - Filter values based on correpsonding boolean array

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(TEXTSPLIT(TEXTJOIN(" ",TRUE, B3:B5),," "),ISNUMBER(SEARCH("@",TEXTSPLIT(TEXTJOIN(" ",TRUE, B3:B5),," "))))

becomes

FILTER({"DD"; "AA"; "[email protected]"; "CC"; "[email protected]"; "AA"; "FF"; "GG"; "HH"; "[email protected]"},{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE})

and returns

{"[email protected]";"[email protected]";"[email protected]"}.

#### Step 6Ā - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

FILTER(TEXTSPLIT(TEXTJOIN(" ",TRUE, B3:B5),," "),ISNUMBER(SEARCH("@",TEXTSPLIT(TEXTJOIN(" ",TRUE, B3:B5),," "))))

TEXTSPLIT(TEXTJOIN(" ",TRUE, B3:B5),," ") is repeated twice in the formula, lets name it x. The formula becomes:

LET(x,TEXTSPLIT(TEXTJOIN(" ",TRUE, B3:B5),," "),FILTER(x,ISNUMBER(SEARCH("@",x))))

## Example 3,

It is possible to combine the array formulas in example 1 and 2, unfortunately, the formula can still only extract one email address per cell.

Array formula in cell B6:

Cell B6 has "Wrap text" enabled, select cell B6 and press CTRL + 1 to open the "Format Cells" dialog box.

## Example 4,

If you need an even better faster formula I recommend using a UDF:

Recommended articles

The image above demonstrates a User Defined Function thatĀ extracts all words containing a given string that you can specify. In […]

## Example 5,

The formula in cell F3 gets only one email address per row so it is very basic, however, check out the comments for more advanced formulas.

If the cell contains an email address and also other text strings it won't extract the email only, as I said, it is a very basic formula.

Array formula in F3:

To enter an array formula, type the formula in cell F3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aĀ beginning and ending curly bracket telling you that you entered the formula successfully.

Don't enter the curly brackets yourself, they appear automatically.

This article demonstrates how to filter emails with a custom function:

Recommended articles

The image above demonstrates a User Defined Function thatĀ extracts all words containing a given string that you can specify. In […]

### Explaining formula in cell F3

#### Step 1 - Look for @ character in cellĀ range

The SEARCH function allows you to find the character position of a substring in a text string, we are, however, not interested in the position only if it exists or not in the cell.

SEARCH("@", B3:D3))

becomes

SEARCH("@", {"BB","[email protected]","CC"}))

and returnsĀ {#VALUE!,5,#VALUE!}. This tells us that the second value in the array contains a @ character on position 5.

#### Step 2 - Convert array to TRUE or FALSE

The IF function can't handle error values in the logical expression so we must first convert the array to boolean values. The ISERROR function returns TRUE if the value is an error value and FALSE if not.

ISERROR(SEARCH("@", B3:D3))

becomes

ISERROR({#VALUE!,5,#VALUE!})

and returnsĀ {TRUE,FALSE,TRUE}.

#### Step 3 - Return column number if value is not an error value

IF(ISERROR(SEARCH("@", B3:D3)), "", MATCH(COLUMN(B3:D3), COLUMN(B3:D3)))

becomes

IF({TRUE,FALSE,TRUE}, "", MATCH(COLUMN(B3:D3), COLUMN(B3:D3)))

To create an array from 1 to n I use the MATCH function and COLUMN function.

IF({TRUE,FALSE,TRUE}, "", MATCH(COLUMN(B3:D3), COLUMN(B3:D3)))

becomes

IF({TRUE,FALSE,TRUE}, "", MATCH({2,3,4}, {2,3,4}))

becomes

IF({TRUE,FALSE,TRUE}, "", {1, 2,3})

and returnsĀ {"", 2,""}

#### Step 4 - Return the smallest column number

The MIN function calculates the smallest number in cell range or array.

MIN(IF(ISERROR(SEARCH("@", B3:D3)), "", MATCH(COLUMN(B3:D3), COLUMN(B3:D3))))

becomes

MIN({"", 2,""})

and returns 2.

#### Step 4 - Return value corresponding to column number

The INDEX function returns a value based on a row and/or column number.

INDEX(B3:D3, MIN(IF(ISERROR(SEARCH("@", B3:D3)), "", MATCH(COLUMN(B3:D3), COLUMN(B3:D3)))))

becomes

INDEX(B3:D3, 2)

and returns "[email protected]".

### Get Excel *.xlsx file

Learn to use regular expressions and filter emails:

Recommended articles

This article demonstrates a User Defined Function that allows you to extract cell references from a formula in a given […]

Learn how to use the LIKE operator to filter emails:

Recommended articles

The LIKE operator allows you to match a string to a pattern using Excel VBA. The image above demonstrates a […]

### Filter emails category

The image above demonstrates a User Defined Function thatĀ extracts all words containing a given string that you can specify. In […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 42 Responses to “How to extract email addresses from an Excel sheet”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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),

Your formula worked great.

Thanks

I get this to work but I have email with ( at beginning and ) at the end of the address how would I modify the formula to remove the () from the address?

worked for me too. thanks for sharing

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:

[email protected] | [email protected] | [email protected] | 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: https://www.get-digital-help.com/2010/08/20/filter-emails-spread-over-several-columns-in-excel/

Guys, just got the 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,[email protected],null,null

mark edwards,

Read Rick Rothstein (MVP - Excel) comment in this post:

https://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"; "[email protected]"; "[Statement not provided]"; "1"

Id like for the result of the formula to be

[email protected]

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

Hello,

Lets say that you have a huge excel sheet with dirty data.

All the email adresses is in the same column but sometimes there is also a phone number next to it.

I have to send all these people a press release so basically I want to collect them and paste them into my email program.

It should look like this when I am finished:

[email protected]; [email protected]; [email protected];

Any good idea?

samuel,

have you read this post:

https://www.get-digital-help.com/2011/03/16/excel-udf-filter-emails-from-an-excel-range/

HI Oscar,

i have excel with a list of email addresses and lot of other contents along with email addresses in a single cell .is there a way to extract only the email addresses that are there in a single cell? .

Sam,

read this post:

Excel udf: Filter emails from an excel range

and Rick Rothstein's (MVP - Excel) comment.

Hey Oscar,

Trying to modify the formula to work with a column without much luck. In column A I have:

A1: John Smith

A2: 123 any street

A3: Any town

A4: Zip

A5: email

Sometimes I may also have a company name so email will be A6. In B1 I want to extract the e-mail address. Any thoughts?

thanks guys, this has been a big help.

How to extract email addresses from a cell where there are multiple email addresses are provided...example given below

"

Jonas Anstton[[email protected]; 46 7 90 99 76; Master Samuelesser 8, DE-34584, Kerala, India]; Fred Wing[[email protected]; +687519245; ]

"

Thank you!

You are welcome.