Custom Number Format Adding Mark (.) Between Number

[muhammad susanto]

hi all....

i need your help..how to make custom number in excel for this situation

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]data[/td][td]after custom formula[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]401010100320[/td][td]4.01.01.01.003.20[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]40101010084584[/td][td]4.01.01.01.008.4584[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]40101020029[/td][td]4.01.01.02.002.9[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]4010102003777[/td][td]etc…[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

any help much appreciated..

m.susanto

 

[cbatrody]

Hi,

Try the below in F2:

=LEFT(E2,1)&"."&MID(E2,2,2)&"."&MID(E2,4,2)&"."&MID(E2,6,2)&"."&MID(E2,8,3)&"."&RIGHT(E2,LEN(E2)-10)

 

[muhammad susanto]

Hi,

Try the below in F2:

=LEFT(E2,1)&"."&MID(E2,2,2)&"."&MID(E2,4,2)&"."&MID(E2,6,2)&"."&MID(E2,8,3)&"."&RIGHT(E2,LEN(E2)-10)

thank but i want in Format Cell--Custom Number ....

 

[Rick Rothstein]

hi all....

i need your help..how to make custom number in excel for this situation

Unfortunately, you can't. Digits fill from right to left in Custom Cell Formatting and your value after the last dot is variable in length and has a need to preserve trailing zeroes when present. Also, some of your numbers are longer than the number of digits Excel can display, so your cells are probably formatted as Text which means you cannot give them a second cell format.

 

[muhammad susanto]

Unfortunately, you can't. Digits fill from right to left in Custom Cell Formatting and your value after the last dot is variable in length and has a need to preserve trailing zeroes when present. Also, some of your numbers are longer than the number of digits Excel can display, so your cells are probably formatted as Text which means you cannot give them a second cell format.

thank you Rick...
how to make it good...?

for eg. 4.01.01.01.003
4.01.01.02.002

 

[Rick Rothstein]

thank you Rick...
how to make it good...?

for eg. 4.01.01.01.003
4.01.01.02.002

For 10-digit numbers only, you can use this Custom Format type pattern....

0\.00\.00\.00\.000

 

[muhammad susanto]

For 10-digit numbers only, you can use this Custom Format type pattern....

0\.00\.00\.00\.000

thank you...worked...