CODEWITHSUNDEEP
excelexcel-2013
pee2pee
pee2pee

Reputation: 3802

Pick Out Email From a Row

I have over 100k rows similar to:

Joe | 123 | [email protected] 
Bob | 456
Ben | 567 | Denver | [email protected]

The emails could be at any point/cell in that row and some cells have @ in them.

How could I get the following output:

Joe | [email protected] 
Bob | 
Ben | [email protected]

or maybe just tag the email address onto the end of the row and then hide the other columns?

I've tried various bits and pieces but am getting nowhere fast.

Upvotes: 0

Views: 62

Answers (2)

Julien Marrec
Julien Marrec

Reputation: 11905

Now that I know you can have it in every column, Add a column left of column A. In your example you then have B1="Joe", etc.

Then put this formula in A1:

=IFERROR(OFFSET(A1,0,SUM(IFERROR(IF(FIND("@",$B1:$O1)>0,1,0),0)*COLUMN($B1:$O1))-1),"")

Adjust the range $B1:$O1 to match your needs. I suggest you make it as tight as possible because array formulas are resource-intensive.

========================

If the email addresses were always in the last column of a given row, and if there weren't any blanks in the row until the last value, you could just do that:

First, Add a column left of column A. In your example you then have B1="Joe", etc.

Then, put this formula in cell A1

=OFFSET(A1,0,COUNTA($B1:$XFD1))

and drag and drop it on all your rows. (I'm using Excel 2010, hence XFD in the above formula. Adjust as you see fit, just make sure you use a range that covers the maximum number of columns for your dataset)

Upvotes: 2

pnuts
pnuts

Reputation: 59495

A bit of fun really. Embolden the names and for the rest Replace All @ without formatting with @ and Font style Bold. Copy into Word, Select All, Find what: * with Font Not Bold, Use wildcards and replace with nothing. Copy back into Excel and Go To Special, Blanks. Right click on one of selection and Delete…, Shift cells left.

Upvotes: 1

Related Questions