Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting data from phone fields (Excel 97/SR2)

    I am being provided a roster of employees in an excel format, and I am trying to clean-up the formatting of the phone number field. Some of the entries come in as all data (4025551919, 4025550010) and some come in with varying degrees of formating (402-555/0101 or 402/555-1010 and others).

    Is their an easy way to start in cell J1 and strip only the numeric character out and place them in the same order in cell M1. Then move to cell J2 and strip out only the numeric characters and place them in cell M2, continuing on until the entire column is completed?

    Any help is appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extracting data from phone fields (Excel 97/SR2)

    Do you mean to eliminate the non-numeric characters? If this is a one time cleanup, you could use Find-&-Replace to delete the non-numerics "(", ")", "?", "-", etc. You will not need extra columns for that process. Then you can run a little formula alongside them to ensure that they are all 10 digits =len(cell)=10 to be certain they are valid US phone numbers. Then it's number by number cleanup time for any errors; give that job to the intern. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    (Then since they all seem to be area 402 you can write them all a message asking how come Nebraska got beat by Colorado. I'm a stunned Husker follower myself. <img src=/S/weep.gif border=0 alt=weep width=21 height=16>)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting data from phone fields (Excel 97/SR2)

    This User Defined Function can be used to do that:

    <pre>Public Function GetNumeric(strVal As String) As String
    Dim strWk As String
    strWk = strVal
    While Len(strWk) > 0
    If IsNumeric(Left(strWk, 1)) Then
    GetNumeric = GetNumeric & Left(strWk, 1)
    End If
    strWk = Right(strWk, Len(strWk) - 1)
    Wend
    End Function
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting data from phone fields (Excel 97/SR2)

    Thanks much Legare. Exactly what I was looking for. Worked like a charm. <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

Posting Permissions

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