Numbers Left And Right Of Decimal.

[jgthtrotih]

Hi,

I have 2 problems..*Formulas Only Please*

Problem #1:

I want to take the number i enter in column "BP" and have it read as "Gallons" (As Shown Below) in column "BQ".


<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 56px;"><col style="width: 132px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>BP</td><td>BQ</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: center;">DATA</td><td style="text-align: center;">SHOULD READ</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: center;">0.05</td><td style="text-align: center;">0.05 Gallons</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: center;">0.25</td><td style="text-align: center;">1/4 Gallons</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: center;">0.68</td><td style="text-align: center;">0.68 Gallons</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: center;">0.75</td><td style="text-align: center;">3/4 Gallons</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="text-align: center;">1</td><td style="text-align: center;">1 Gallon</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="text-align: center;">1.25</td><td style="text-align: center;">1 and 1/4 Gallons</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="text-align: center;">2</td><td style="text-align: center;">2 Gallons</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td style="text-align: center;">2.51</td><td style="text-align: center;">2.51 Gallons</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="text-align: center;">5.47</td><td style="text-align: center;">5.47 Gallons</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="text-align: center;">250</td><td style="text-align: center;">250 Gallons</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="text-align: center;">1000.75</td><td style="text-align: center;">1000 and 3/4 Gallons</td></tr></tbody></table>
Problem #2:

I need to split the "Decimal-ed number" into two columns..i have gotten the formula for the "Left Side" but i need help with the "Right Side". (As Shown Below)


<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 67px;"><col style="width: 42px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>BU</td><td>BV</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="text-align: center;">1000.75</td><td style="text-align: right;">1000</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="text-align: center;">24502.52</td><td style="text-align: right;">24502</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="text-align: center;">16035.26</td><td style="text-align: right;">16035</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="text-align: center;">25.36</td><td style="text-align: right;">25</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="text-align: center;">1.2</td><td style="text-align: right;">1</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>BV14</td><td>=IF(ISERROR(LEFT(BU14,FIND(".",BU14,1)-1))=TRUE,"",LEFT(BU14,FIND(".",BU14,1)-1))</td></tr></tbody></table></td></tr></tbody></table>

Using Windows Xp With Sp2 With Excel 2003.

Pleas Ask Questions If Any.

Thanks!

 

[jgthtrotih]

Post removed!

 

[jgthtrotih]

Mickrickson,

Almost there how do i remove the spaces?

Its not showing up but for example in my workbook "BQ8" resluts in "1 (space) (space) Gallon"

Excel Workbook
	BP	BQ
7	0.75	3/4 gallons
8	1	1 gallon
9	1.25	1 and 1/4 gallons
10	2	2 gallons
11	2.51	2.51 gallons
12	5.47	5.47 gallons
13	250	250 gallons
14	1000.75	1000 and 3/4 gallons
Sheet13

Thanks!

 

[Aladin Akyurek]

BQ4:

=IF(TRUNC(BP4)=0,"",TRUNC(BP4)&IF(MOD(BP4,1)," and ",""))&
  IF(MOD(BP4,1),IF(MOD(MOD(BP4,1),0.25)=0,
    TEXT(MOD(BP4,1),"# ?/?"),MOD(BP4,1)),"")&" gallon(s)"

BV14:

=IF(TRUNC(BU14),TRUNC(BU14),"")

BW14:

=REPLACE(BU14,1,FIND(".",BU14),"")+0

 

[jgthtrotih]

Aladin,

Thanks Works!

What is Trunc?

Thanks!

 

[Aladin Akyurek]

Aladin,

Thanks Works!

What is Trunc?

Thanks!

=INT(4.2) ==> 4
=INT(-4.2) ==> 5

=TRUNC(4.2) ==> 4
=TRUNC(-4.2) ==> 4

TRUNC truncates the numbers at the decimal point.

 

[jgthtrotih]

Aladin,

I got it now.

Thanks!

 

[Biz]

Mickrickson,

Almost there how do i remove the spaces?

Its not showing up but for example in my workbook "BQ8" resluts in "1 (space) (space) Gallon"

Excel Workbook
	BP	BQ
7	0.75	3/4 gallons
8	1	1 gallon
9	1.25	1 and 1/4 gallons
10	2	2 gallons
11	2.51	2.51 gallons
12	5.47	5.47 gallons
13	250	250 gallons
14	1000.75	1000 and 3/4 gallons
Sheet13

Thanks!

Use Trim formula
=TRIM(TEXT(BP7,IF(CEILING(BP7,0.25)=BP7,"# ""and"" ?/?","0.00")& """ gallon"""&IF(BP7=1,"","""s""")))

 

[jgthtrotih]

Biz,

Sorry for the late reply.

Thanks that worked!

Thanks!

 

[Biz]

Biz,

Sorry for the late reply.

Thanks that worked!

Thanks!

No Worries