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)
Related blog posts
- Excel udf: Filter emails from an excel range
- How to filter values between 0.5 and 1.5 from two columns in excel 2007
- 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
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
September 19th, 2010 at 3:39 pm
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?
September 19th, 2010 at 3:40 pm
P.P.S> where to send the beer money?
September 22nd, 2010 at 7:06 am
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
November 8th, 2011 at 7:13 am
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
November 9th, 2011 at 8:17 am
Jon,
You are right! Thanks for your contribution!