Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
|
#4
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert dollars and cents to text, and prefill the cell . | Excel Worksheet Functions | |||
converting #dollars into word dollars? | Excel Worksheet Functions | |||
Amount or Numbers in Words | New Users to Excel |