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

email-1.png

Answer:

email-2.png

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:

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)