+ Reply to Thread
Results 1 to 3 of 3

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

Hybrid View

jz789 Macro or Function to extract... 01-28-2013, 12:01 PM
MitchC Re: Macro or Function to... 01-28-2013, 12:39 PM
jz789 Re: Macro or Function to... 01-28-2013, 10:31 PM
  1. #1
    Registered User
    Join Date
    01-26-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    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: quoyien@Test.com
    2. Home: 123-330-5000 Email: menithtkernik@Test.com
    3. Owner Cell: 123-029-0055 Email: eene-eoni@Test.com
    4. Home: 123-002-0327 Work: 212-000-9001 Cell: 000-092-3302 E-meil: yen_nine@Test.com
    5. Home: 000-229-2029 Work: 123-900-0101 Cell: 999-010-5030
    6. Owner: 123-239-0300 E-Meil: yesonBerenbech@Test.com
    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: tem_meTe@Test.com
    10. Owner: 123-379-9011 E-Meil: teov72@Test.com
    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: etubremenien70@Test.com
    14. Hm: 123.002.1005 Cell: 510.057.0109 Email: meehethukle02@Test.com
    15. Owner: 123-505-2700 Cell: 123-503-0779 Email: thethikenTeurTe@Test.com
    16. Owner Home: 123-510-0901 Cell: 123-509-0000 Email: ebdur_02@Test.com

    Here is what the expected output should look like:
    Expected Output

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

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

  2. #2
    Registered User
    Join Date
    08-12-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    25

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

    This will do the trick...kind of a sloppy solution, but it will work with your data.

    testing_extract_ph_email.xlsx

  3. #3
    Registered User
    Join Date
    01-26-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    Thanks Mitch. That worked very well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1