Closed Thread
Results 1 to 2 of 2

Data normalizing for phone number extraction

  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    Moon
    MS-Off Ver
    Excel 2013
    Posts
    20

    Data normalizing for phone number extraction

    I need some help finishing normalizing data in following example below.I understand it would have been better to start with correct formatting, but I'm trying to migrate from this old solution to a new product and need to extract as much data as I can in correct fields.

    I need help to figure out how to:

    1. To get rid off any text if there is e.g. "OFFICE: Jamie 11111111" to make "OFFICE: 11111111", or "OFFICE: no phone number" to "OFFICE: " or "EMERGENCY: Jenny 1111111111" to "EMERGENCY: 1111111111". I do NOT care about "Some random text" because final goal is to extract data after HOME: OFFICE: CELL: EMERGENCY: as per earlier forum post
    2. If there is any 7 digit number, I need to add 000 to beginning of number, e.g. "OFFICE: 1111111" to "OFFICE: 0001111111"
    3. If there is any 3 digit long number, delete it.

    HOME: 1111111111 OFFICE: no phone number
    HOME: 1111111111 OFFICE: 1111111
    HOME: 1111111111 OFFICE: 1111111111 EMERGENCY: 1111111111
    HOME: 1111111111 OFFICE: EMERGENCY: 1111111111
    HOME: OFFICE: EMERGENCY: 1111111111 CELL: 1111111111
    HOME: 1111111 OFFICE: 1111111111 EMERGENCY: Jenny 1111111111 CELL: 1111111111
    HOME: 1111111111 CELL: 1111111 OFFICE: 1111111111 EMERGENCY: Julie 1111111111
    HOME: 1111111111 OFFICE: 1111111111 Some random text HOME: 1111111111 OFFICE: EMERGENCY:
    HOME: 000 OFFICE: 1111111111 EMERGENCY: Tara 1111111111 CELL: 1111111111
    HOME: 1111111111 Some random text OFFICE: 1111111111 HOME: 1111111111 OFFICE: EMERGENCY:

    thank for help in advance

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Data normalizing for phone number extraction

    isn't this thread same as your link provided?

    if it's same continue on that thread since members are providing solutions there just add those instances of data in your sample file.

    PM me if this thread is not same as the other one. Will be closing it for now.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

Closed 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