Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting Numbers Into Different Columns
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lots of numbers and columns | Excel Discussion (Misc queries) | |||
how to freez more five columns without splitting the panes? | Excel Worksheet Functions | |||
2 columns with duplicate names and total numbers | Excel Discussion (Misc queries) | |||
Add numbers accross columns after stripping away text | Excel Discussion (Misc queries) | |||
how to build a formula to match numbers in 2 columns with the equ. | Excel Worksheet Functions |