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

This article demonstrates how to filter emails:

Excel udf: Filter emails from an excel range

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:

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:

extract_email_2.xls
(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 […]

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)