Number format iniciating with 0 (zero)

[davidalverne]

Hi,
I'm having serious problems trying to create a number format.
What's the formula to creat this format: 013.435.253-06 ?
Thank you

 

[Kurt]

Hello David,

What version of Excel are you using?

I would think you would have to create a custom Number Format.

HTH

Kurt

 

[davidalverne]

Thank you Kurt,

I'm using Excel 2007. I allready tried to create a custom number format. The big problem is the 0 in the begining and the number of digits.
I tried the format ###.###.###-##, and also 000.000.000-00, but when I tip 01343525306, Excel keeps giving me 1.343.525.3-06 or 01.343.525.3-06.

What should I do?

 

[pagrender]

There is a way you can make it work with a formula. If you type 1343525306 into A1 (the zero automatically disappears from 01343525306), you can use this formula in B1:

="0"&LEFT(A1,2)&"."&MID(A1,3,3)&"."&MID(A1,6,3)&"-"&RIGHT(A1,2)

Pete

 

[davidalverne]

Thank you,
I tried the formula above, and it didn't work out. Besides, when it happens that I have to type more then one 0? Like 000.325.444-75

 

[pagrender]

I guess if your data isn't the same length (meaning it isn't necessarily a zero in the first character of your number string), you can use this formula:

=IF(LEN(A1)=10,"0"&LEFT(A1,2)&"."&MID(A1,3,3)&"."&MID(A1,6,3)&"-"&RIGHT(A1,2),LEFT(A1,3)&"."&MID(A1,4,3)&"."&MID(A1,7,3)&"-"&RIGHT(A1,2))

 

[pagrender]

After reading your post, it seems like a number format is your best choice. It would probably be a VERY long formula to make every possible scenerio.

Sorry that I'm not much help.

Thank you,
But when it happens that I have to type more then one 0? Like 000.325.444-75

 

[jc.021286]

for the number formats, try the following number format

0##"."###"."###-##

the above gave me the what you wished
013.435.253-06
note: excel still gets rid of the extra zero though displays it.

Also, if your first 3 digits are > than 100
123.456.789-12 appears instead, eliminating the leading zero.

Hope this helps,
jc

 

[pagrender]

Maybe it's not too long of a formula - this works for a number length from 8-11 characters:

=IF(LEN(A1)=10,"0"&LEFT(A1,2)&"."&MID(A1,3,3)&"."&MID(A1,6,3)&"-"&RIGHT(A1,2),IF(LEN(A1)=9,"00"&LEFT(A1,1)&"."&MID(A1,2,3)&"."&MID(A1,5,3)&"-"&RIGHT(A1,2),IF(LEN(A1)=8,"000"&"."&LEFT(A1,3)&"."&MID(A1,4,3)&"-"&RIGHT(A1,2),LEFT(A1,3)&"."&MID(A1,4,3)&"."&MID(A1,7,3)&"-"&RIGHT(A1,2))))

 

[Kurt]

Just oustide looking in, but what happens when there are different numbers in place of the digits?

I think Vog or somone would know of the vba code that would do this.