Phone Numbers

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

Hello,

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,