Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
alison via OfficeKB.com
 
Posts: n/a
Default Separating dollars and cents

I have a spread sheet that has a dollar/cents amount (ie 23.47) and I'd
like to separate it into two different cells ( ie 23 in one cell labeled
dollars and 47 in another ). This is for a mail merge where the word
documents states 23 dollars and 47 cents.

I'm able to get a result using =LEFT on the dollars and =MID with the cents
BUT if the amount is 123.00 I get errors on both dollars and cents

the actual functions are
=LEFT(T3,FIND(".",T3)-1)
=MID(T3,FIND(".",T3,1)+1,LEN(T3))

thanks for all and any help

--
Message posted via http://www.officekb.com
  #2   Report Post  
Jason Morin
 
Posts: n/a
Default

Dollars:

=ROUNDDOWN(T1,0)

Cents:

=MOD(T1,1)-(T1<0)

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a spread sheet that has a dollar/cents amount (ie

23.47) and I'd
like to separate it into two different cells ( ie 23 in

one cell labeled
dollars and 47 in another ). This is for a mail merge

where the word
documents states 23 dollars and 47 cents.

I'm able to get a result using =LEFT on the dollars and

=MID with the cents
BUT if the amount is 123.00 I get errors on both dollars

and cents

the actual functions are
=LEFT(T3,FIND(".",T3)-1)
=MID(T3,FIND(".",T3,1)+1,LEN(T3))

thanks for all and any help

--
Message posted via http://www.officekb.com
.

  #3   Report Post  
alison via OfficeKB.com
 
Posts: n/a
Default

Thank you thank you thank you

It worked WONDERFULLY

--
Message posted via http://www.officekb.com
  #4   Report Post  
GerryK
 
Posts: n/a
Default

As an example, start off with the dollars part thus:
With 23.47 in A1 put this formula in B1;

=IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".",A1)-1),LEFT
(A1,1))&" dollars"

modify as needed for the cents part in C1.

HTH

-----Original Message-----
I have a spread sheet that has a dollar/cents amount (ie

23.47) and I'd
like to separate it into two different cells ( ie 23 in

one cell labeled
dollars and 47 in another ). This is for a mail merge

where the word
documents states 23 dollars and 47 cents.

I'm able to get a result using =LEFT on the dollars and

=MID with the cents
BUT if the amount is 123.00 I get errors on both dollars

and cents

the actual functions are
=LEFT(T3,FIND(".",T3)-1)
=MID(T3,FIND(".",T3,1)+1,LEN(T3))

thanks for all and any help

--
Message posted via http://www.officekb.com
.

  #5   Report Post  
Alan Perkins
 
Posts: n/a
Default

one way:

Dollars = int(T3)
cents = T3 - Dollars
HTH

Alan P.

"alison via OfficeKB.com" wrote in message
...
I have a spread sheet that has a dollar/cents amount (ie 23.47) and I'd
like to separate it into two different cells ( ie 23 in one cell labeled
dollars and 47 in another ). This is for a mail merge where the word
documents states 23 dollars and 47 cents.

I'm able to get a result using =LEFT on the dollars and =MID with the
cents
BUT if the amount is 123.00 I get errors on both dollars and cents

the actual functions are
=LEFT(T3,FIND(".",T3)-1)
=MID(T3,FIND(".",T3,1)+1,LEN(T3))

thanks for all and any help

--
Message posted via http://www.officekb.com





  #6   Report Post  
Jason Morin
 
Posts: n/a
Default

INT() works fine for positive values, but if the OP has
negative dollar values, then it rounds "up". For example:

=INT(-4.35) = -5

Jason

-----Original Message-----
one way:

Dollars = int(T3)
cents = T3 - Dollars
HTH

Alan P.

"alison via OfficeKB.com" wrote in

message
m...
I have a spread sheet that has a dollar/cents amount

(ie 23.47) and I'd
like to separate it into two different cells ( ie 23

in one cell labeled
dollars and 47 in another ). This is for a mail merge

where the word
documents states 23 dollars and 47 cents.

I'm able to get a result using =LEFT on the dollars

and =MID with the
cents
BUT if the amount is 123.00 I get errors on both

dollars and cents

the actual functions are
=LEFT(T3,FIND(".",T3)-1)
=MID(T3,FIND(".",T3,1)+1,LEN(T3))

thanks for all and any help

--
Message posted via http://www.officekb.com



.

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 do I convert dollars and cents to text, and prefill the cell . Jan Buckley Excel Worksheet Functions 1 January 20th 05 08:22 PM
converting #dollars into word dollars? hotrodenford Excel Worksheet Functions 2 January 7th 05 07:29 AM
Amount or Numbers in Words ron New Users to Excel 6 December 24th 04 08:32 PM


All times are GMT +1. The time now is 04:44 PM.

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

About Us

"It's about Microsoft Excel"