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

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

Download excel sample file for this tutorial:

(Excel 97-2003 Workbook *.xls)

Recommended blog post:

Excel udf: Filter emails from an excel range

External resources


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

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)