numbers

T

tankgirl215

I've got a huge sheet of data that I'm trying to change into a specific
format. I'm putting formulas on sheet 1 referencing the data on sheet
2 but putting it into the format I need. The only problem is with the
numbers.

On the data sheet I have weights and measurements in this format:

0.1500 8.5000 3.0000 2.2500
0.2500 7.2500 2.2500 2.0000
0.1000 8.5000 3.0000 1.7500

That needs to be on the other sheet in this format:

00001500 00085000 00030000 00022500
00002500 00072500 00022500 00020000
00001000 00085000 00030000 00017500

Ignoring the extra spacing between, how can I get them into that
format? The final product needs to be 8 characters, right justified,
zero filled, and has an implied decimal place between the first 4 and
the last 4 spaces.

Thanks!
 
P

Pete_UK

Assume your data starts in A1 on Sheet2. Enter this formula in A1 of
Sheet1:

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

Copy the formula into B1, C1 and D1, then highlight these 4 cells and
copy down for as many entries as you have in Sheet2.

Hope this helps.

Pete
 
T

tankgirl215

Awesome - It worked! Thanks so much!


Pete_UK said:
Assume your data starts in A1 on Sheet2. Enter this formula in A1 of
Sheet1:

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

Copy the formula into B1, C1 and D1, then highlight these 4 cells and
copy down for as many entries as you have in Sheet2.

Hope this helps.

Pete
Click to expand...
 
R

Ron Rosenfeld

I've got a huge sheet of data that I'm trying to change into a specific
format. I'm putting formulas on sheet 1 referencing the data on sheet
2 but putting it into the format I need. The only problem is with the
numbers.

On the data sheet I have weights and measurements in this format:

0.1500 8.5000 3.0000 2.2500
0.2500 7.2500 2.2500 2.0000
0.1000 8.5000 3.0000 1.7500

That needs to be on the other sheet in this format:

00001500 00085000 00030000 00022500
00002500 00072500 00022500 00020000
00001000 00085000 00030000 00017500

Ignoring the extra spacing between, how can I get them into that
format? The final product needs to be 8 characters, right justified,
zero filled, and has an implied decimal place between the first 4 and
the last 4 spaces.

Thanks!
Click to expand...

=TEXT(A1*10^4,"00000000")


--ron
 
Top