How to Extract all Email Addresses in Excel

if you have a sheet that looks like the following:
|    |              A               |                B             |           C               |
| 1 |        David             |           Home          | abc@123.com  |
| 2 |   def@456.com    |          Susan           |       School         |
| 3 |         Office            |     ghi@789.com   |         Mike         |

and you want to extract all of the email addresses

type something like “mail” in the top row of the farthest new column, which would be column “D” here

then in the cell directly below that, which is cell D2 in the example below, paste the following code:

=INDEX(A1:C1, 1, MIN(IF(ISERROR(SEARCH(“@”, A1:C1)), “”, COLUMN(A1:C1))))

then while the cursor is still at the end of the text line in the text bar at the top, hit CTRL + SHIFT + ENTER.

one result should appear.

then copy cell D2 and select all the way down to the bottom, or one row past the bottom, and hit Paste

|    |              A               |                B             |           C               |          D              |
| 1 |        David             |           Home          | abc@123.com  |       Mail:          |
| 2 |   def@456.com    |          Susan           |       School         |                          |
| 3 |         Office            |     ghi@789.com   |         Mike         |                          |

then copy cell D2 and select all the way down to the bottom, or one row past the bottom,
and hit Paste

which will result in the following:

|    |              A               |                B             |           C               |          D              |
| 1 |        David             |           Home          | abc@123.com  |       Mail:          |
| 2 |   def@456.com    |          Susan           |       School         | abc@123.com |
| 3 |         Office            |     ghi@789.com  |         Mike          |  def@456.com |
| 4 |                                |                               |                            | ghi@789.com  |

Giving you all the Emails in one list

Info:
=INDEX(A1:C1, 1, MIN(IF(ISERROR(SEARCH(“@”, A1:C1)), “”, COLUMN(A1:C1))))

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s