Article updated on February 05, 2018

Question: How to extract email addresses from this sheet? (See pic below)




Array formula in D2:

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

This article demonstrates how to filter emails:

Filter emails from an excel range [UDF]

This udf extracts all words containing a specified string. Example, Cell range B1:M50 contains random sentences. I have inserted some […]

Learn how to use the LIKE operator to filter emails:

Working with the LIKE OPERATOR

The LIKE operator allows you to match a string to a pattern in excel vba. The following characters are specifically […]

How to 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

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Download excel sample file for this tutorial:

(Excel 97-2003 Workbook *.xls)

Learn to use regular expressions and filter emails:

Extract cell references from a formula

I am trying to build a regular expression that matches cell references in a formula. A regular expression is a […]


Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Checks whether a value is an error and returns TRUE or FALSE

Returns the column number of a reference

Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)