Extracting specific information from rows

[marketingFWi]

We have a list that includes all information in different rows. We need to extract email addresses and the corresponding name into 2 separate columns. For example, this is the data in A1-A19:

[TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Brett Abarbanel[/TD]
[/TR]
[TR]
[TD]University of California, Los Angeles[/TD]
[/TR]
[TR]
[TD]760 Westwood Plz[/TD]
[/TR]
[TR]
[TD]Los Angeles, CA 90095-8353[/TD]
[/TR]
[TR]
[TD]UNITED STATES (310)206-9942[/TD]
[/TR]
[TR]
[TD]Gregory Abbott[/TD]
[/TR]
[TR]
[TD]SVP Travel & Hospitality[/TD]
[/TR]
[TR]
[TD]DataArt[/TD]
[/TR]
[TR]
[TD]475 Park Ave S Fl 15[/TD]
[/TR]
[TR]
[TD]New York, NY 10016-6901[/TD]
[/TR]
[TR]
[TD]UNITED STATES (757)478-3184[/TD]
[/TR]
[TR]
[TD]gabbott@dataart.com[/TD]
[/TR]
[TR]
[TD]Mark Abraham[/TD]
[/TR]
[TR]
[TD]VP of Revenue Services[/TD]
[/TR]
[TR]
[TD]Luxe Hotels[/TD]
[/TR]
[TR]
[TD]11491 W Sunset Blvd[/TD]
[/TR]
[TR]
[TD]Los Angeles, CA 90049-2031[/TD]
[/TR]
[TR]
[TD]UNITED STATES (310)691-7712[/TD]
[/TR]
[TR]
[TD]mark@luxehotels.com
[/TD]
[/TR]
</tbody>[/TABLE]


I need to do one of two things:

1) break out each type of data into columns (Name, Title, Address, City/State, Zip, Country, Phone, Phone Number) so I can then work with the data

2) extract the email address and corresponding names. So pull out gabbott@dataart.com and indicate that it is related to the name Gregory Abbott. If it is not possible to correspond the email address to the name, just extracting all of the email addresses will suffice.

Thank you,
Emily

 

[Rukt]

Emily,

Because your raw data isn't particularly "structured" (e.g. Gregory and Mark have 7 rows of data, but Brett only has 5), it might be difficult to reliably separate the data into the columns you mention.

Some are easy enough, though. Note that email addresses always have an "@" symbol (and in your data at least, are the only things that do). Similarly, phone numbers are all formatted (XXX)XXX-XXX. We can use this to identify those types of data.

If you put this formula in the column next to your data, and copy/paste it down, it should grab email addresses:

=IF(ISNUMBER(SEARCH("@",A1)),A1,"")

Similarly, this one should grab phone numbers:

=IF(ISNUMBER(SEARCH("(???)???-????",A1)),A1,"")

Admittedly, that might not make the data much easier to work with. Does it get you any closer?

Rukt

 

[marketingFWi]

Hi Rukt,

This is great, we can at least use the email addresses which will be a huge help. Thank you!

Emily