Google-sheets – How to format Brazilian phone numbers in Google Sheets

formulasgoogle sheetsgoogle-sheets-cell-format

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?

Best Answer

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

Related Topic