numbers

T

tankgirl215

Help!

I need to take numbers on one sheet in a workbook and insert them into
another sheet in a slightly different format. I need help figuring out
a formula to put into the sheet they need to be on in the end, that
will reference the cell with the original data but also change its
format.

The format needs to change from 3.25 -> 00000032500
The decimal point needs to be removed as you can see.

Any ideas?

Thanks!
 
B

Bill Ridgeway

I'm not sure I fully understand your motive so I'll take your question quite
literally. If on you second work sheet you type "=" (without quote marks)
and then take the cursor to the source cell and press enter, that will take
the raw data. To gross this figure up and to add the leading zeros the
formula should read something like -
="000000"&A1*10000

This solution will only work if you want 5 digit numbers preceded by six
zeros and if data is not sorted otherwise it is a bit limited.

Regards.

Bill Ridgeway
Computer Solutions
 
H

Harlan Grove

tankgirl215 wrote...
I need to take numbers on one sheet in a workbook and insert them into
another sheet in a slightly different format. I need help figuring out
a formula to put into the sheet they need to be on in the end, that
will reference the cell with the original data but also change its
format.

The format needs to change from 3.25 -> 00000032500
The decimal point needs to be removed as you can see.
Click to expand...

If you don't need to perform calculations using the second set of
numbers, use formulas like

=SUBSTITUTE(TEXT(x,"0000000.0000"),".","")
 
T

tankgirl215

Ok, well here's what I'm going from:

I've got a column of numbers 1.25
10.25
3.25

and they need to be on a different sheet in this format 00000012500

00000102500

00000032500

For another, similar formatting issue I was working on before, someone
gave me this formula:

=REPT("0",4-LEN(INT(SHEET2!A1)))&INT(SHEET2!A1)&TEXT(MOD(SHEET2!A1,1)*10000,"0000")

This was for changing 3.25 to 00032500...similar, but fewer 0's and
still with the implied decimal 4 positions from the right.

How do I edit this formula to do what I need it to this time?

Thanks!
 
B

Bill Ridgeway

This is an alternative to my suggestion. The REPT function forces a
character a stated number of times so -
==REPT("0",4
repeats 0(zero) four times and the remainder of the formula seems to deal
with getting the data, re-formatting and concatenating (joining) it. If you
want to use your formula and change the number of leading zeros amend the 4
to 3 or whatever.

My formula -
="000000"&A1*10000
could be expressed -
=REPT(0",4)&A1*10000

Regards.

Bill Ridgeway
Computer Solutions
 
T

tankgirl215

The only problem with that is that the number of 0's in front isn't
set. If the number being changed is 10.25, it doesn't work. I need to
find a way to make sure it starts filling in from the right instead...
like two zeros after the number always, and then just as many as needed
before it to fill it up to the maximum 11 characters in the field.

00000102500 --> 00000 1025 00
this is the this is the this will be
unknown number from 2 zeros
zeros to be the original
filled in sheet w/o "."
 
S

SteveG

tankgirl,

If you always want 2 zeros to the right try,

=REPT("0",SUM(11-LEN(SUBSTITUTE(A1&"00",".",""))))&SUBSTITUTE(A1&"00",".","")

HTH,

Steve
 
B

Bill Ridgeway

I did comment on this in a previous post. It is easy to resolve - although
it does complicate the formula.
Changing the formula -
="000000"&A1*10000
to
=IF(A1<10,"000000"&A1*10000,"00000"&A1*10000)
will detect if the number is less than 10 and include an additional 0 (zero)

Changing -
=REPT(0",4)&A1*10000
to
=IF(A1<10,REPT(0",4)&A1*10000,REPT(0",3)&A1*10000)
will do the same.

Regards.

Bill Ridgeway
Computer Solutions
 
Top