Remember Me?

#1
August 23rd 11, 07:52 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2010 Posts: 23
divide numbers up for a paper form

Ok, here are two separate form problems I have.

Form 1 requires whole dollars in one column, and the cents in
another.

ie 27,651.09 needs to be "27,651" in one column and "09" in the one
right next to it. These numbers are formula driven totals calculated
elsewhere that must be transformed for this one form, as archaic as
the form happens to be.

Form 2 is worse, this would take a number like "27,651.09" and divide
it one digit at a time for the approximate box on the printed form.

ie 2|7|6|5|1|0|9|

I tried =text type formulas with the idea of extrapolating which
places I needed for the respective boxes, but that is not working out
well.

Ideas greatly appreciated.

Bruce

#2
August 23rd 11, 10:23 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2011 Posts: 25
divide numbers up for a paper form

Suppose your calculated value is in A1. This will give you the integer
value:

=INT(A1)

but if you need it as text then do this:

=TEXT(INT(A1),"#,##0")

The following will give you the cents, with leading zeros preserved:

=TEXT((A1-INT(A1))*100,"00")

Hope this helps.

Pete

On Aug 23, 7:52*pm, Revenue wrote:
Ok, here are two separate form problems I have.

Form 1 requires whole dollars in one column, and the cents in
another.

ie *27,651.09 needs to be * "27,651" in one column and "09" in the one
right next to it. These numbers are formula driven totals calculated
elsewhere that must be transformed for this one form, as archaic as
the form happens to be.

Form 2 is worse, this would take a number like "27,651.09" and divide
it one digit at a time for the approximate box on the printed form.

ie * 2|7|6|5|1|0|9|

I tried =text type formulas with the idea of extrapolating which
places I needed for the respective boxes, but that is not working out
well.

Ideas greatly appreciated.

Bruce

#3
August 23rd 11, 11:29 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2010 Posts: 1,522
divide numbers up for a paper form

On Aug 23, 4:23*pm, Pete_UK wrote:
Suppose your calculated value is in A1. This will give you the integer
value:

=INT(A1)

but if you need it as text then do this:

=TEXT(INT(A1),"#,##0")

The following will give you the cents, with leading zeros preserved:

=TEXT((A1-INT(A1))*100,"00")

Hope this helps.

Pete

On Aug 23, 7:52*pm, Revenue wrote:

Ok, here are two separate form problems I have.

Form 1 requires whole dollars in one column, and the cents in
another.

ie *27,651.09 needs to be * "27,651" in one column and "09" in the one
right next to it. These numbers are formula driven totals calculated
elsewhere that must be transformed for this one form, as archaic as
the form happens to be.

Form 2 is worse, this would take a number like "27,651.09" and divide
it one digit at a time for the approximate box on the printed form.

ie * 2|7|6|5|1|0|9|

I tried =text type formulas with the idea of extrapolating which
places I needed for the respective boxes, but that is not working out
well.

Ideas greatly appreciated.

Bruce- Hide quoted text -

- Show quoted text -

datatext to columnsdelimitedother = .

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post mar Excel Worksheet Functions 2 September 26th 09 08:20 AM accounting Excel Discussion (Misc queries) 3 January 25th 07 06:13 PM NEYS Excel Worksheet Functions 5 August 17th 06 08:48 AM [email protected] Excel Discussion (Misc queries) 6 October 16th 05 05:00 PM mohammad Excel Discussion (Misc queries) 2 December 6th 04 01:41 PM

All times are GMT +1. The time now is 10:32 AM.