Filter emails spread over several columns in excel
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
Answer:
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
Download excel template
Filter emails spread over several columns.xls
(Excel 97- 2003 Workbook *.xls)
Functions in this article:
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
SMALL(array,k) returns the k-th smallest row number in this data set.
ROW(reference) returns the rownumber of a reference
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
COLUMN(reference) returns the column number of a reference
ISNUMBER(value)
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)
Related posts:
- Filter duplicate values in a range using “contain” condition in excel
- Filter unique text values in a range using “contain” condition in excel
- Filter unique distinct text values in a range using “contain” condition in excel
- Vlookup of three columns to pull a single record
- Filter unique distinct values where adjacent cells contain search string in excel
- Lookup with multiple criteria and display multiple search results using excel formula, part 4
- Filter unique distinct values using “contain” condition of a column in excel
- Lookup two index columns returning multiple matches in excel
- Lookup two index columns using min max values and a date range as criteria
- Lookup two index columns in excel











August 17th, 2010 at 4:30 pm
This works great! the only issue I am having is on my end. I realized that for some reason my table has a space in front of some of the words so cells A2:C6 might have a space before the double letter. There is no way that I can get around this because I am pasting this table from somewhere else and the table is too large to delete all the spaces. Is there a way to make the formula ignore the space before the words? Thanks!
ex:
A2:BB C2:DD
A3: AA C3:AA
A4: CC
see how A3 and C3 are slightly different because A3: has a space before AA and that causes the formula to only then display C3's data when the search says "AA" and not " AA"
August 17th, 2010 at 6:14 pm
@Arielle,
Just TRIM the range...
=INDEX($A$1:$C$1, SMALL(IF(COUNTIF($E$1, TRIM($A$2:$C$6))>0, COLUMN($A$2:$C$6)), ROW(A1)))