Extract Email Address

[bwatts5]

I am trying to extract email address from columns and display them in another column. The way the information is extracted causes the email address to appear in columns.
Example:
A

 

[bwatts5]

Some how i posted this too soon.
I am trying to extract email address from columns and display them in another column. The way the information is extracted/imported causes the email address to appear in columns. I would like column E to display email, F phone number, and G Occupation.
Example:

Mark	111-2222	Mark@mark.com	Driver
Bill	Attendant	Home	bill@yahoo.com
Bob	555-1212	Server
Steve	Server	555-1212
Larry	Driver	111-2222

<colgroup><col style="mso-width-source:userset;mso-width-alt:5083;width:104pt" width="139"> <col style="mso-width-source:userset;mso-width-alt:8594;width:176pt" width="235"> <col style="mso-width-source:userset;mso-width-alt:4352;width:89pt" width="119"> <col style="mso-width-source:userset;mso-width-alt:9142;width:188pt" width="250"> </colgroup><tbody>
[TD="width: 139"]Mike [/TD]
[TD="width: 235"]driver[/TD]
[TD="width: 119"]555-1213
[/TD]
[TD="class: xl65, width: 250"] Mike@mike.com
[/TD]

[TD="class: xl65"] bob@bo.com [/TD]

[TD="class: xl65"] steve@steve.com [/TD]

[TD="class: xl65"] Larry@larry.com [/TD]

</tbody>

Thank you

 

[Rick Rothstein]

I am trying to extract email address from columns and display them in another column. The way the information is extracted causes the email address to appear in columns.
Example:
A

Something went wrong with your example as it did not post with your message.

 

[bwatts5]

Thanks for noticing. I am looking for a way to get column E = Occupation, F = phone, G= Email.


[TABLE="width: 743"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Mike [/TD]
[TD]driver[/TD]
[TD]555-1213[/TD]
[TD]Mike@mike.com[/TD]
[/TR]
[TR]
[TD]Mark [/TD]
[TD]111-2222[/TD]
[TD] Mark@mark.com[/TD]
[TD]Driver[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Attendant[/TD]
[TD]Home[/TD]
[TD] bill@yahoo.com[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]bob@bo.com[/TD]
[TD]555-1212[/TD]
[TD]Server[/TD]
[/TR]
[TR]
[TD]Steve [/TD]
[TD]Server[/TD]
[TD]steve@steve.com[/TD]
[TD]555-1212[/TD]
[/TR]
[TR]
[TD]Larry [/TD]
[TD]Driver[/TD]
[TD]111-2222[/TD]
[TD]Larry@larry.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it. Please be patient and do not attempt to post the question again.

I approved the posts, and deleted a few duplicates you had too.

 

[Rick Rothstein]

Here is a formula that will pull the email addresses into its cell...

=INDEX(B1:D1,1,MATCH("*@*",B1:D1,0))

and you can get the phone number using this formula...

=INDEX(B1:D1,1,MATCH("*-*",B1:D1,0))

 

[Rick Rothstein]

Here is a formula that will pull the email addresses into its cell...

=INDEX(B1:D1,1,MATCH("*@*",B1:D1,0))

and you can get the phone number using this formula...

=INDEX(B1:D1,1,MATCH("*-*",B1:D1,0))

Reposting with extra solution info...

Here is a formula that will pull the email addresses into its cell...

=INDEX(B1:D1,1,MATCH("*@*",B1:D1,0))

and you can get the phone number using this formula...

=IFERROR(INDEX(B1:D1,1,MATCH("*-*",B1:D1,0)),"Home")

although I am not sure if "Home" is always going to be the right answer when an actual telephone number is not given.

Given the possibility that "Home" (a text value) could be there, I am not entirely sure how to make sure we only get the occupation when looking for that item.

 

[DanteAmor]

I present 2 alternatives to find the occupation. (The formulas for the telephone and email are the same)


In the first, it is array formula (to enter press Shift + Control + Enter) and it will bring you a text, in the example it says "Home", because there are "Attendant" and "Home", then the formula takes the second.

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">NAME</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATA1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATA2</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATA3</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Occupation</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Phone</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Email</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Mike</td><td >driver</td><td >555-1213</td><td >Mike@mike.com</td><td >driver</td><td >555-1213</td><td >Mike@mike.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Mark</td><td >111-2222</td><td >Mark@mark.com</td><td >Driver</td><td >Driver</td><td >111-2222</td><td >Mark@mark.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Bill</td><td >Attendant</td><td >Home</td><td >bill@yahoo.com</td><td >Home</td><td >Does not have</td><td >bill@yahoo.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Bob</td><td >bob@bo.com</td><td >555-1212</td><td >Server</td><td >Server</td><td >555-1212</td><td >bob@bo.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Steve</td><td >Server</td><td >steve@steve.com</td><td >555-1212</td><td >Server</td><td >555-1212</td><td >steve@steve.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Larry</td><td >Driver</td><td >111-2222</td><td >any</td><td >any</td><td >111-2222</td><td >Does not have</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >{=INDEX($A2:$D2,1,MAX(((--ISNUMBER(SEARCH("-",$B2:$D2))=0)*((--ISNUMBER(SEARCH("@",$B2:$D2))=0))*(COLUMN($B2:$D2)))))}</td></tr><tr><td >F2</td><td >=IFERROR(HLOOKUP("*-*",$B2:$D2,1,0),"Does not have")</td></tr><tr><td >G2</td><td >=IFERROR(HLOOKUP("*@*",$B2:$D2,1,0),"Does not have")</td></tr></table></td></tr></table> <br /><br />

-------
In the second, it is a normal formula and if you find more than one text then it gives you a warning.

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /><col style="width:177px;" /><col style="width:117px;" /><col style="width:117px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">NAME</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATA1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATA2</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATA3</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Occupation</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Phone</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Email</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Mike</td><td >driver</td><td >555-1213</td><td >Mike@mike.com</td><td >driver</td><td >555-1213</td><td >Mike@mike.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Mark</td><td >111-2222</td><td >Mark@mark.com</td><td >Driver</td><td >Driver</td><td >111-2222</td><td >Mark@mark.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Bill</td><td >Attendant</td><td >Home</td><td >bill@yahoo.com</td><td >More than one coincidence</td><td >Does not have</td><td >bill@yahoo.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Bob</td><td >bob@bo.com</td><td >555-1212</td><td >Server</td><td >Server</td><td >555-1212</td><td >bob@bo.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Steve</td><td >Server</td><td >steve@steve.com</td><td >555-1212</td><td >Server</td><td >555-1212</td><td >steve@steve.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Larry</td><td >Driver</td><td >111-2222</td><td >any</td><td >More than one coincidence</td><td >111-2222</td><td >Does not have</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IFERROR(INDEX($A2:$D2,1,SUMPRODUCT(((--ISNUMBER(SEARCH("-",$B2:$D2))=0)*((--ISNUMBER(SEARCH("@",$B2:$D2))=0))*(COLUMN($B2:$D2))))),"More than one coincidence")</td></tr><tr><td >F2</td><td >=IFERROR(HLOOKUP("*-*",$B2:$D2,1,0),"Does not have")</td></tr><tr><td >G2</td><td >=IFERROR(HLOOKUP("*@*",$B2:$D2,1,0),"Does not have")</td></tr></table></td></tr></table> <br /><br />

 

[bwatts5]

THANK YOU BOTH. They both work great! MUCH APPRECIATED, Saved hours of work.

Bill

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.