Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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 = .


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to divide numbers mar Excel Worksheet Functions 2 September 26th 09 08:20 AM
Can I scan an existing paper form into excel accounting Excel Discussion (Misc queries) 3 January 25th 07 06:13 PM
DIVIDE NUMBERS EVENLY NEYS Excel Worksheet Functions 5 August 17th 06 08:48 AM
Numbers divide by 10 in any cell... [email protected] Excel Discussion (Misc queries) 6 October 16th 05 05:00 PM
My excel2000 divide numbers per 10 why? mohammad Excel Discussion (Misc queries) 2 December 6th 04 01:41 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017