How to format a 20 digits numbers in GSHEETS (or EXCEL)

Question

I am using GSHEETS
I am trying to use a format number to customise the display of a 20 digits number, example:
Input in the cell: 12345678901234567890
desired cell formatting: 12 345678 9012345678 90

I tried this: 00_000000_0000000000_00
but it dipslays 12 345678 9012345000 00

I tried this: 00"-"000000"-"00000000"-"00
but it displays this: 1234-567890-12345000-00

It seems that it's possible to format only 15 numbers. Is that right or am I mistaking?

Answer 1

The following is a Google Sheets only solution.

If you have a 20 digit number in a cell and it is effectively a string, then this will format it as you need it (it still will be a string):

=REGEXREPLACE(A1, "^(\d{2})(\d{6})(\d{10})(\d{2})$", "$1 $2 $3 $4")

UPDATE. And here are a few others.

Not only a number but any string will be formatted with spaces:

=REGEXREPLACE(B2, "^(.{2})(.{6})(.{10})(.{2})$", "$1 $2 $3 $4")

This one lets you change the delimiter in one place:

=JOIN(" ", REGEXEXTRACT(B4, "^(\d{2})(\d{6})(\d{10})(\d{2})$"))

And a silly one:

=REGEXREPLACE(B3, "^(..)(......)(..........)(..)$", "$1 $2 $3 $4")