Extraction email from a list

[luis_marques]

Hi,

I would like to extract email from a list. The list was originally created in PDF (from a outside source which i dont have Word file) and copied it to a Excel workbook. That is a example from the list:
In cell A1 the following information altogether separated by comma. I changed the text i want to extract (email) to red color to facilitate your identification:

Distribuição,AES,Sul,Distribuidora,Gaúcha,de,Energia,Guilherme,de,Mauro,Favaron,guilherme.favaron@aes.com,(11),2195-7306

Thanks in advance

Luis

 

[Luke M]

Select your data, go to Data - TextToColumns. In the Wizard, select Delimiter, and then select comma. Hit Finish/Ok. This will split out your data. You should then have your email extracted. If not, this array formula will find the cell with email and put in a new column:
=INDEX(A1:N1,MAX(IF(ISNUMBER(FIND("@",A1:N1)),COLUMN(A1:N1))))

Remeber to confirm with Ctrl+Shift+Enter, not just Enter.

 

[json]

Luke, could you break down that formula?

 

[iferror]

json, the best way is to break down the formula and then use the F9 to see the result.
This is how i would do:
copy the sample line and paste in A1
copy the formula and paste in B1
then start breaking down
paste the FIND("@",A1:N1) bit in C1, CTRL+SHIFT+ENTER to confirm and then select the cell and press F9
move to D1 and add a bit of the formula
and so on

 

[Luke M]

I'll give the explanation a shot.

After we split the data into columns, we'll have a row of cells with different texts in each one. We want to find out which one has a "@" in it, and return that cell.
The FIND function will return the location of a string within a cell, or an error is the string is not found. So, because we're using arrays, the FIND function will produce an array full of errors, except for the cell with our email address, which will give a number. Example:
{#VALUE!,#VALUE!,#VALUE!,#VALUE!, 7, #VALUE!,#VALUE!}.

The ISNUMBER function will convert that into a True/False array:
{FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE}

The IF function then takes that array, and everywhere we have a TRUE, we return the corresponding COLUMN number. (Note that col E = col 5)
{FALSE,FALSE,FALSE,FALSE,5,FALSE,FALSE}

The MAX function changes our array into a single value, extracting our lone number, 5.

The INDEX function then takes that number, and uses is to return the 5th cell from the array given in first arguement. In our example, the 5th cell is E1, and this would be our final result.

 

[json]

BRAVO!! Awesome job!
I award you 50 Gold Internet Points!!!


So this works mainly because there is only 1 cell in the array that contains the '@' symbol. If there were multiple cells/cols, it would return the 'last' on due to the max function.... i assume we could use the MIN fucntion if we wanted to return the first col with the @ symbol as well...

interesting!!


guys, I guess we'll keep Luke around a bit longer!! HAHAHHA

 

[Luke M]

So this works mainly because there is only 1 cell in the array that contains the '@' symbol. If there were multiple cells/cols, it would return the 'last' one due to the max function.... i assume we could use the MIN function if we wanted to return the first col with the @ symbol as well...

Exactly! Glad I've earned my keep for a little while longer, lol.

 

[json]

yeah.... just a bit longer though.... i wouldn't get too comfortable! HAHAHAHA

 

[Haseeb A]

Hello Luis,

As Luke said, text to columns is a way. Once you done, you could use HLOOKUP,

=HLOOKUP("*@*",A1:N1,1,0)

Or try this without Text to columns,

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(",",A1,FIND("@",A1))-1),",",REPT(" ",99)),99))

 

[luis_marques]

Thanks Luke M and Haseeb. Both solutions worked superb.

 

[Luke M]

Just when I think I've got a pretty slick formula, Haseeb comes along and points out a simple, elegant solution. I tip my hat to you, sir.

 

[json]

point adjustment:

35 Internet Points to Haseeb!!!
15 to Luke

hahahaha