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:

Brett Abarbanel

University of California, Los Angeles

760 Westwood Plz

Los Angeles, CA 90095-8353

UNITED STATES (310)206-9942

Gregory Abbott

SVP Travel & Hospitality

DataArt

475 Park Ave S Fl 15

New York, NY 10016-6901

UNITED STATES (757)478-3184

gabbott@dataart.com

Mark Abraham

VP of Revenue Services

Luxe Hotels

11491 W Sunset Blvd

Los Angeles, CA 90049-2031

UNITED STATES (310)691-7712

mark@luxehotels.com

<colgroup><col></colgroup><tbody>
</tbody>

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