help : adding zeo number

[muhammad susanto]

hello all,

i have a problem to adding/insert zero number with multiple condition for this which total character number is 12

sample data (excel format general)	after formula
2005-6-12	200506000012
1997-11-7	199711000007
2001-10-358	200110000358
1985-5-4521	198505004521
2002-12-8	200212000008
etc...

<tbody>
</tbody>

it's possible with excel formula to figure out?

thanks for your helping...

m.susanto

 

[FormR]

One possible way, based on the sample data:

Excel Workbook
	A	B
1	sample data	after formula
2	2005-6-12	200506000012
3	1997-11-7	199711000007
4	2001-10-358	200110000358
5	1985-5-4521	198505004521
6	2002-12-8	200212000008
Sheet1

 

[muhammad susanto]

FormR, it's awesome...i'am glad now...

many-many thanks!!!!

 

[XOR LX]

An alternative:

=SUMPRODUCT((TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",255)),255*(ROW(INDIRECT("1:"&1+LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1)+1,255))*(10^{8;6;0})))

Regards

 

[muhammad susanto]

XOR LX,it's good running....

thanks..