Author: Oscar Cronquist Article last updated on July 23, 2017

magneticone asks:

Any idea how to adapt the formula to do a conditional (i.e. comma-separated) merge of the data from multiple columns, in the case that a single row has emails spread over several columns?

You can find magneticone´s question here: How to extract email addresses from a excel sheet


Array formula in E2:

=INDEX($A$1:$C$3, SMALL(IF(ISNUMBER(SEARCH("@", $A$1:$C$3)), ROW($A$1:$C$3)-MIN(ROW($A$1:$C$3))+1, ""), ROW(A1)), (SMALL(IF(ISNUMBER(SEARCH("@", $A$1:$C$3)), (ROW($A$1:$C$3)-MIN(ROW($A$1:$C$3))+1)+COLUMN($A$1:$C$3)/1048576, ""), ROW(A1))-SMALL(IF(ISNUMBER(SEARCH("@", $A$1:$C$3)), ROW($A$1:$C$3)-MIN(ROW($A$1:$C$3))+1), ROW(A1)))*1048576) + CTRL + SHIFT + ENTER.

Copycell E2 and paste it down as far as needed.

Every cell containing "@" is filtered into column E.

Concatenate cells in column E

Unfortunately you need some vba code to pull this one off: VBA Join function or maybe not, read this: Concatenate a cell range without vba in excel

Recommended blog post

Excel udf: Filter emails from an excel range

Download excel template

Filter emails spread over several columns.xls
(Excel 97- 2003 Workbook *.xls)

Functions in this article:

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

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

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

COLUMN(reference) returns the column number of a reference

Checks whether a value is a number and returns TRUE or FALSE

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)