Splitting Numbers Into Different Columns
 

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

Splitting Numbers Into Different Columns
 

try selecting cellsright clickformatcustom 0000

--
Don Guillett
SalesAid Software

"Peter W Soady (UK)" wrote in message
...
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

Splitting Numbers Into Different Columns
 

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

Splitting Numbers Into Different Columns
 

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")



"Peter W Soady (UK)" wrote:

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