Splitting Numbers Into Different Columns

  • Thread starter Thread starter Peter W Soady \(UK\)
  • Start date Start date
P

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