Results 1 to 2 of 2

Thread: Phone Numbers

  1. #1

    Phone Numbers

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I just became a member and I'm looking for assistant with a formula. Here is my scenario. I have a database dump with data in a spreadsheet that includes thousands of phone number that are formatted may different ways. Ex. 1222-2222222,1(222)222-2222, 2222222222, 222222-22222x(2222) or 222222222-2222. The format I'm looking for is (222)-222-2222. I tried this formula REPLACE(SUBSTITUTE(SUBSTITUTE(L2, " ",""),0,0) I received an error and it entered 0 in the cell because the Instance_num field was empty. I also tried using the existing Phone Number format in excel by formatting the entire column and re-typing the first three cell in the column; drag from the third cell down but it change the phone number. Can you help to with a formula that will insert parenthesis into the first three numbers and then insert dashes?
    Thank you

  2. #2

    Assuming I've accounted for all of the inconsistencies in the format I think this will work. Assuming phone numbers start in a1, then in b1 use :: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")",""),"x","") **remove all none numbers** and in c1 :: TEXT(IF(LEFT(B1,1)="1",LEFT(B1,10),"1"&LEFT(B1,9)),"0(000)-000-000") **add a 1 if it doesn't being with a 1 and then format the left 10 characters as denoted.

    Hope this helps,

Posting Permissions

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