Instead of trying to write code that deals with messy data... is it possible for you to clean the data and make it more standardized?

Impossible i'm not in charge of that database and it gets replicated daily with new data that is being added to it on a daily basis. The data is being added by hundreds of people weekly and there is no error checking on that database so making it impossible for me to adjust and make it right. Sorry but I can't make it standerized it's out of my hands.

Ok, I'm working on getting the function to work on all those possiblities. But if a new possibility pops up that's not coded for it will break. There really should be error checking on the data entry side as this is not really the best place to do it as you can see.

I understand and I have told them that but they refused to re-code it due that it's been in place for some years now and they don't have the time to do it.. well they will have to deal with the other errors that will show up..then probably they will decide to do something??

Ok, here we go. The function has gotten quite a bit more comlicated. I've tested it pretty well and it will format almost any number you throw at it. Try it out and let me know if you have any trouble.

Function FormatPhoneNumber(strNumber)
    dim ext
    dim tmp_str
    dim x
    
    tmp_str = lcase(strReverse(strNumber))
    for x = 1 to len(tmp_str)
        if not isnumeric(mid(tmp_str, x, 1)) then
            'found a possible ext, let's pull it out.
            ext = strReverse(left(tmp_str, x -1))
            strNumber = strReverse(right(tmp_str, len(tmp_str) - x))
            exit for
        end if
    next 'x
    
    'lets remove all existing formating
    'We want the number to end up looking like this: 1234567890
    tmp_str = ""
    for x = 1 to len(strNumber)
        if isnumeric(mid(strNumber, x, 1)) then
            tmp_str = tmp_str & mid(strNumber, x, 1)
        end if
    next 'x
    strNumber = tmp_str

    'if there is 11 digits and the first digit is 1 then
    if len(strNumber) = 11 and left(strNumber, 1) = "1" then
        'grab the last 10 characters
        strNumber = Right(strNumber, 10)
    elseif len(strNumber) <> 10 then 
        if len(strNumber) + len(ext) <> 10 then
            FormatPhoneNumber = ""
            exit function
        else
            strNumber = strNumber & ext
            ext = ""
        end if
    end if  

    if len(ext) > 0 then ext = "Ext: " & ext

    'reformat the number
    strNumber = "(" & Left(strNumber, 3) & ") " & Mid(strNumber, 4, 3) & "-" & Right(strNumber, 4)
    
    'tack on an extension if there is one:
    if len(ext) > 0 then
        strNumber = strNumber & " " & ext
    end if
        
    FormatPhoneNumber = strNumber

End Function

Hey buddy I give you a 5***** rating you rock!! It works nicely so far thanks a bunch you really helped me out. I hope one day to be as good as you so that I could help out big time the people in need of scripting.

Again Thanks.

Glad it's working for you