# Number format iniciating with 0 (zero)

#### davidalverne

##### New Member
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

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

#### Kurt

##### Well-known Member
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
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
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

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

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

Hope this helps,
jc

#### pagrender

##### Well-known Member
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
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.

Replies
1
Views
323
Replies
2
Views
181
Replies
1
Views
161
Replies
4
Views
94
Replies
0
Views
256

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.

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.

### Which adblocker are you using?

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

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