Splitting Numbers Into Different Columns

[Peter W Soady \(UK\)]

Hi

I have a small problem which I can partly solve but not fully. The scenario
is following:

I have columns formated as number with 2 decimal places however I need to
split these columns into whole number and decimal number but I need the
whole number to be 4 digits in length. Example

New Columns
Original Col Whole Num Decimal Num
0.12 0000 12
12.12 0012 12
120.12 0120 12

I am not interested in the decimal point but the columns must conform to the
above or the Oracle Application will reject the import

I have tried different ways using the IF and LEN but seem to be banging
against the brick wall. I would accept any way of doing this, Macro, VBA or
Function. Howecver if it is macro or VBA I will also require the knowledge
on how to attach this to the worksheet

Any help would be greatly appreciated


Peter W S

 

[Don Guillett]

try selecting cells>right click>format>custom 0000

 

[Carim]

Hi Peter

Assuming numbers are column A :
in column B just type formula =Int(A1) and then Format Cells Number
Custom 0000
and copy this formula all the way down
in column C just type formula = B1-A1 and Format Cells Number Number 2
decimals

HTH
Cheers
Carim

 

[Guest]

I've never tried to import anything into an Oracle application, so I'm not
sure whether or not your application will accept numbers that are only
formatted to display 4 digits. If the other suggestions don't work, maybe

=TEXT(INT(A1), "0000")