Formatting a Number Field

[abardel]

I am using Excel 97 and I need to do the following:


I have a column of values that look like this:

12.23
15.55
14.15 etc

I need to format each of them to look like this:

000000001223
000000001555
000000001415

Any help would be greatly appreciated. thanks

 

[NBVC]

Try This

=TEXT(SUBSTITUTE(A1,".",""),"000000000000")

 

[Smitty]

Will this do it for you:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> Macro1()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([A1], [A65536].End(xlUp))
            c = c * 100
            c.NumberFormat = "00000000####"
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[Ste_Moore01]

I've had a try with on my computer and I can't see an easy way of formatting it.

I've come up with a formula though that you could use if you created an other column.

Assuming your first value is in A1 the formula would be..
=TEXT(A1,"00000000##"&MID(A1,(FIND(".",A1,1)+1),2))

If you want it as a number (i.e. 1223 but with 00000000) change it to
=VALUE(TEXT(A1,"00000000##"&MID(A1,(FIND(".",A1,1)+1),2)))
But you would need to right-click and format the cell as custom, and the type would be 00000000##.

Hope that helps!

 

[abardel]

Try This

=TEXT(SUBSTITUTE(A1,".",""),"000000000000")

That returned 0.00

 

[abardel]

If you want it as a number (i.e. 1223 but with 00000000) change it to
=VALUE(TEXT(A1,"00000000##"&MID(A1,(FIND(".",A1,1)+1),2)))
But you would need to right-click and format the cell as custom, and the type would be 00000000##.

This one worked, kinda....

My value was 16.85 and it returned 0000000017 (looks like it rounded up)

I really need it to show 000000001685???

 

[NBVC]

Try This

=TEXT(SUBSTITUTE(A1,".",""),"000000000000")

That returned 0.00

This is what I got:

Book1 (version 1).xls
	E	F	G	H
16	12.23	000000001223
Unit A

Are you sure your original data is in A1? In my example it is E16 and the formula references E16

 

[abardel]

Are you sure your original data is in A1? In my example it is E16 and the formula references E16[/quote]

Thanks for the follow up, I'll try it again!

 

[abardel]

That returned 0.00[/quote]

This is what I got:


I got it to work, thanks so much!!

 

[abardel]

Are you sure your original data is in A1? In my example it is E16 and the formula references E16[/quote]

Another question??? The ones that are 10.00, I need to show as 000000001000 (not as displayed below), and the one that is 12.12, I need to show as 000000001212 (not as below)

Any suggestions?

16.85 000000001685
0.00 000000000000
8.82 000000000882
8.82 000000000882
10.00 000000000010
10.00 000000000010
10.00 000000000010
10.96 000000001096
12.00 000000000012
12.12 000012115385
12.50 000000000125
13.25 000000001325
14.66 000000001466
15.68 000000001568
17.21 000000001721
20.72 000000002072
20.72 000000002072
20.72 000000002072
20.72 000000002072