Number format iniciating with 0 (zero)

davidalverne

New Member
Joined
Sep 8, 2010
Messages
5
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Hello David,

What version of Excel are you using?

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

HTH

Kurt
 

davidalverne

New Member
Joined
Sep 8, 2010
Messages
5
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

Well-known Member
Joined
Sep 3, 2008
Messages
652
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

New Member
Joined
Sep 8, 2010
Messages
5

ADVERTISEMENT

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
 
Last edited:

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
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

Well-known Member
Joined
Sep 3, 2008
Messages
652

ADVERTISEMENT

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

Well-known Member
Joined
Apr 12, 2010
Messages
725
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

Well-known Member
Joined
Sep 3, 2008
Messages
652
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

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,855
Messages
5,766,781
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top