May 03 2018 07:29 AM
May 03 2018 07:29 AM
What format can I use in order to display 89462046211305792124 (20 numbers) as "8946 2046 2113 0579 2124" (displayed in group of four digits)?
May 03 2018 08:12 AM
Hi Erik,
If you keep it as text, to my knowledge it's not possible to insert any symbols into the text by formatting.
If keep as number Excel rounds it to 15 digits (max for numbers representations), but for numbers in general you may apply grouping using custom format like
0000 0000 0000 0000 0000
May 03 2018 08:18 AM - edited May 03 2018 08:22 AM
Yes, this is exactly the problem, because I exceed 15 digits, I need to convert it to text (otherwise excel will change the exceeding digits to zeros..) to avoid the problem, but then the format code you suggest will not work. Any other ideas? I have heard in some cases you can use @ or ? I think (to represent either text or digits, but I am not sure if this is correct)
May 03 2018 08:50 AM
Erik, no other ideas if only not to convert by formulas (into other column) or by macro.
With @ as placeholder you may only add prefix
May 03 2018 09:51 AM
Erik,
you could type the digits with the spaces.
Or you could use Power Query or Flash Fill or a formula:
=REPLACE(REPLACE(REPLACE(REPLACE(A1,17,0," "),13,0," "),9,0," "),5,0," ")