## 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:**

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:**

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

### 5 Responses to “Filter emails spread over several columns in excel”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Wowowow.... that's why you're the best!

I was just thinking when reviewing this awesome update to the previous email extract formula, could we add a placeholder column at E which gets updated dynamically. Then, instead of listing the emails down the length of that column, we could just add each multiple emails immediately to the right column. So columns F,G,H (or however many data columns we have worth) would hold the data in any cell which contains an email... and E would also just be a merge of those (which we could apply the VBA comma-separated filter and multi-email extraction code from the first example).

So the current table would become:

http://sep19.imgshare.us/JCX.png

Where E is a comma-separated list of email-containing columns, and the empty D column would become our final list of emails only.

Hard to say exactly where to make these changes since there's so much going on in that formula, but for starters I think I'd need to switch the references to ROW()+1 into COLUMN()+1?

P.S> What is the role of the magic number 1048576?

P.P.S> where to send the beer money?

magneticone,

I modified Rick Rothsteins ConCat udf.

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String

Dim Cell As Range, Area As Variant

If IsMissing(Delimiter) Then Delimiter = ""

For Each Area In CellRanges

If TypeName(Area) = "Range" Then

For Each Cell In Area

If Len(Cell.Value) And InStr(Cell.Value, "@") Then ConCat = ConCat & Delimiter & Cell.Value

Next

Else

ConCat = ConCat & Delimiter & Area

End If

Next

ConCat = Mid(ConCat, Len(Delimiter) + 1)

End Function

How to implement user defined function in excel

1. Press Alt-F11 to open visual basic editor

2. Click Module on the Insert menu

3. Copy and paste the above user defined function

4. Exit visual basic editor

5. Select a cell

7. Type =ConCat(", ", A1:D4) into formula bar and press ENTER

Your formula above works only if the range you are querying begins in column A. A modified version which works with ranges that don't start in the 1st column is here:

=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)-MIN(COLUMN($A$1:$C$3))+1)/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)

Jon

Jon,

You are right! Thanks for your contribution!