Problem with Excel number masks

[ironsand]

Morning!
I'm experiencing a hard time trying to figure out a number format mask in Excel. The number format is quite simple: just numbers, dots and a hyphen. The format is: 0003475-09.2014.8.05.0001 (7 numbers, hyphen, 2 numbers, dot, 4 numbers, dot, 1 number, dot, 2 numbers, dot, 4 numbers).

I put the following mask in Excel:
0000000-00"."0000"."0"."00"."0000

Here is the trick: Excel put the number in the given mask, but it always changes the 2 last numbers to zeroes ("00").

E.g., if I feed the number 00034750920148059987, I get back the result 0003475-09.2014.8.05.9900. If I feed the number 00034750920148054567, the result is 0003475-09.2014.8.05.4500.



Can somebody help me, please?

[excelledsoftware]

very peculiar. It seems that a custom format like that always wants to end with 2 zeroes. Must have something to do with Scientific Notation. If is possible to do what you want using cells formatted as text and then the following formula in a corresponding cell.

=MID(G1,1,7)&"-"&MID(G1,8,2)&"."&MID(G1,10,4)&"."&MID(G1,14,1)&"."&MID(G1,15,2)&"."&RIGHT(G1,4)

Now of course I put the number in G1 put you can put the value where you want and then change the formula accordingly.

Sorry this isnt exactly what you are looking for but we could also write a VBA script which will keep it in the same cell after you process the code or on worksheet change. Lots of options here.

Thanks

[ironsand]

Though it is not exactly what I wanted, it will work. I just have to adjust the formula a little to fill the leading zeroes, but it is no problem.
This solution answer my need, but my curiosity really thrives to understand what does lead excel to the original misinterpretation.
Thanks for your time! Your answer is a fair solution to my problem!

[Catalin.B]

Hi,
On Excel, numbers must conform to IEEE 757 standards of 15 characters or less, any digit beyond 15 is replaced by "0".
That's why you need to treat numbers with more that 15 chars as text.

[ironsand]

Ooooown!
Ok, now I understand.
Thank you for spending your time providing me a solution and an answer, guys.