I have two possible phone numbers:

One with 9 digits (mobile) and 8 digits (landline), and I need to insert - after 4 or 5 digits so they appear as 0000-0000 when 8 digits, and 00000-0000 when they are 9 digits.

Also, need to get a country and state codes like this +00 (00) the numbers inside the parentheses are the state codes.

I managed to get a formula that works with one or another, but not both. Can anyone help?

"+"?? (##) ????-???? > Works for 8 digit numbers

"+"## (##) ?????-???? > Works with 9 digits.

Any help, please?


like this:

=IF(AND(A1>9999999;  A1<=99999999);  "+"&"55"&" ("&"00"&") "&LEFT(A1;4)&"-"&RIGHT(A1;4);
 IF(AND(A1>99999999; A1<=999999999); "+"&"55"&" ("&"00"&") "&LEFT(A1;5)&"-"&RIGHT(A1;4);

and then "00" / "55" can be referenced from a cell for ultimate comfort

  • How to use this formula? Sorry I don't get it – Freedo Jul 29 '18 at 4:03
  • @Freedo simply put your brazilian phone number in A1 cell and then this formula in B1 cell. formula checks if the number is 8 digit long or 9 digit long and then based on that it will add stuff you requested – user0 Jul 29 '18 at 7:24
  • @Freedo >> if A1 is number larger then 9999999 and smaller or equal to 99999999 (eg. all 8 digit possibilities you can ever have), then add "+" followed by "55" followed by " (" followed by "00" followed by ") " followed by 8 digit number separated in the middle with "-" (eg 4 digits from left side of the number from A1 cell and 4 digits from right side of the number from A1 cell) – user0 Jul 29 '18 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.