Custom format that works for both 8 and 9 digits phone numbers

Question

I want to format a cell to properly display phone numbers, but the number of accepted digits can be either 8 or 9.

I am currently using the following format:
"387" ## ### ###

This works properly for 8-digit numbers:
"387 12 345 678"

I want 9 digits numbers to display as:
"387 12 345 6789"

But the format returns:
"387 123 456 789"

How can I format the cell to return the preferred format for both 8 and 9-digit phone numbers?

Answer 1

Try this custom format:

[<99999999999]### ## ### ###;[<999999999999]### ## ### ####;0

which in practice means: if the number consists of 11 digits, format it one way, if it consists of 12 digits, format it in another, if more DO NOT format it.

Otherwise you could consider using a formula (which would take up a new column) like:

=index(if(len(A2:A)<16,text(A2:A,"### ## ### "& rept ("#",mod(len(N(A2:A)),8))),"error: too many digits"))

Answer 2

Daniele's formatting solution correctly answers your question.

Here is an additional formula-based solution using REGEXREPLACE.

Formula

=ARRAYFORMULA(IFERROR("387 "& 
   REGEXREPLACE(TEXT(1/(1/A2:A),"#"),
     "^(\d{2})(\d{3})", "$1 $2 ")))

Explanation

1. Use ARRAYFORMULA to apply the formula to the entire column
2. Divide each cell value by itself to trigger a #DIV/0! error whenever a cell is empty. IFERROR will replace them with an empty value.
3. TEXT converts each number to a string
4. "387 " is joined to the string returned by REGEXREPLACE which adds a space after the second and fifth characters of the string