Extracting data from phone fields (Excel 97/SR2)

Topic

New Reply

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.

Reply | Quote

Replies

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.

(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. )

Reply | Quote

This User Defined Function can be used to do that:

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

Reply | Quote

Thanks much Legare. Exactly what I was looking for. Worked like a charm.

Reply | Quote