Macro or Function to extract phone numbers and emails from a string

[jz789]

Hello Excel Gurus,
I have a challenge at hand that I am trying to solve. Your help will be much appreciated.

I have a string that contains various phone numbers like Home, Work, Cell. The string may also contain an email address. I need to extract all the different phone numbers into the appropriate columns for home, cell, work as well as the email address.
Here is what a sample data set looks like:

Sample Data Set

1.	Owner Home: 123-321-0002 Cell: 123-701-5700 Email: [email protected]
2.	Home: 123-330-5000 Email: [email protected]
3.	Owner Cell: 123-029-0055 Email: [email protected]
4.	Home: 123-002-0327 Work: 212-000-9001 Cell: 000-092-3302 E-meil: [email protected]
5.	Home: 000-229-2029 Work: 123-900-0101 Cell: 999-010-5030
6.	Owner: 123-239-0300 E-Meil: [email protected]
7.	Work: 999-399-2591 Cell: 201-900-1070
8.	Home: 123-035-1009 Cell: 000-072-0900 Work: 123-752-0207
9.	Owner Home: 123-002-0007 Cell: 000-391-0307 Email: [email protected]
10.	Owner: 123-379-9011 E-Meil: [email protected]
11.	Home: 123-002-0333 Cell: 123-201-2021
12.	Home: 123-771-5500 Work: 201-703-1050
13.	Owner Cell: 123-091-1770 Email: [email protected]
14.	Hm: 123.002.1005 Cell: 510.057.0109 Email: [email protected]
15.	Owner: 123-505-2700 Cell: 123-503-0779 Email: [email protected]
16.	Owner Home: 123-510-0901 Cell: 123-509-0000 Email: [email protected]

Here is what the expected output should look like:

Expected Output

#	Home	Work	Cell	Email
1	123-321-0002		123-701-5700	[email protected]
2	123-330-5000			[email protected]
3			123-029-0055	[email protected]
4	123-002-0327	212-000-9001	000-092-3302	[email protected]
5	000-229-2029	123-900-0101	999-010-5030
6	123-239-0300			[email protected]
7		999-399-2591	201-900-1070
8	123-035-1009	123-752-0207	000-072-0900
9	123-002-0007		000-391-0307	[email protected]
10	123-379-9011			[email protected]
11	123-002-0333		123-201-2021
12	123-771-5500	201-703-1050
13			123-091-1770	[email protected]
14	123.002.1005		510.057.0109	[email protected]
15	123-505-2700		123-503-0779	[email protected]
16	123-510-0901		123-509-0000	[email protected]

I have attached a sample file with sample data.
Any help is much appreciated.
Thanks.
-jz.

[Pete_UK]

The attached file shows how it can be done using formulae in columns C to F which can be copied down as far as you need to.

Note, however, that there are some inconsistencies in your data, as identified by the colours. The word Home is missing from some entries, and so the formula cannot identify that phone number. The word Email was spelt as E-Meil in 3 instances (which I changed), and these would prevent the emails being extracted. Also, there is some inconsistency with the number format, though this does not prevent the formulae from working.

I would suggest that you highlight column A first, then do CTRL-H (Find & Replace), then change "Owner:" to "Owner Home:", then again and change "E-Meil" to "Email" (without the quotes in all cases).

Hope this helps.

Pete

[jz789]

Thanks Mate. That worked well. Appreciate your help as always!