tricky formatting Q (perhaps not so tricky)

[Jim Lally]

.......this has been driving my wife and i crazy......but it's probably easy
stuff for some of you.

we have a list of currency amounts....$23.61, $54.67, $110.00, $54.30

in place of those numbers, what we would like are:

00002361, 00005467, 00011000, 00005430

so basically wanting to have an eight digit number, with zeros preceeding
the point that the number is displayed such that it is the last X number of
places to complete the 8 digit requirement)

is there a way to first do a check for the length of the currency
number....length as in number of places......and then return the zeros to
result in the eight digit number.

i hope i am explaining that in a non confusing manner.

thanks in advance...my wife is convinced that it can't be done.

(e-mail address removed)

 

[Roger Govier]

Hi Jim

with the first value in A1, and the rest going down the column.
Select cell B1, Format>Cells>Number>Custom>00000000
Then enter formula
=A1*100
Copy down as far as required.

 

[KL]

Step1
=A1*100

Step2
menu: Format>Cell..., tab: Number, category: 'Custom', type: 00000000

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[jan]

Jim,

The amount in A2 and then

=TEXT(100*A2,"00000000")

produces realy text strings of 8 digits.
By using format options instead, you get only the look of 8 digits.
Its up to you what it has to be.

Jan