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

user_9010225 0
Viewed 455

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?

google-sheets number-formatting excel
4 Answers

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")

enter image description here

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")

enter image description here

user_2798060

My two cents:

=REPLACE(REPLACE(REPLACE(A1,19,0," "),9,0," "),3,0," ")

enter image description here

user_48515900

Use two cells.

With the full 20 character numeral-string in cell A1, in B1 enter:

=LEFT(A1,2) & " " & MID(A1,3,6) & " " & MID(A1,9,10) & " " & RIGHT(A1,2)

enter image description here

This will work just as well with a 20 character alphabetic string.

enter image description here

user_24746560

When you working string values, maybe:

=TEXTJOIN(" ",1,MID(A1,{1,3,9,19},{2,6,10,2}))

Or even:

=CONCAT(MID(A1,{1;3;9;19},{2;6;10;2})&" ")

I believe for Google-Sheets you'd need CONCATENATE.

user_97581940
Related