For example:

I want column A to read "5 gal" and column B read "$25.00" and column C=A/B.
But when I enter that formula it won't compute beause in column A i have
"gal". I know an easy solution would be to add another column to read the
"gal" but I would really like to find a way to only have the 3 columns.

Any help please would be greatly appreciated. Thank you!

CarJO129 wrote:
For example:

I want column A to read "5 gal" and column B read "$25.00" and column C=A/B.
But when I enter that formula it won't compute beause in column A i have
"gal". I know an easy solution would be to add another column to read the
"gal" but I would really like to find a way to only have the 3 columns.

Any help please would be greatly appreciated. Thank you!

An easy way is to use column headers, where A1=Amount(gal) , B1=Price,
and C1=$/gal. And perhaps your formula should be C=B/A unless you really
do want gallons per dollar.
Then you only have to enter the number in the rest of the column and
save typing four characters -- the space is also a character -- for each
entry.
Otherwise your formula for column C is going to get a lot more complicated.

hi
yes but you would have to use a more complicated formula.
with words and numbers in a cell, excel treats in a all text ie no math
possible so what you have to do is pick out the number.
to pick out the 5 from 5 gals use this formula
=left(A2,1)
so that your intire formula would be...
=left(A2,1)/B2
the above left formula only pick out 1 digit so if you have more that one
digit, say, 10 gal, then you would have to modify the left formula to
compensate. or expand the formula.....
=left(A2,find(" ",A2,1))/B2
the above formula searches for a space and picks out all preceding numbers
before the space.
read up on the left and find functions in excel help. also look at the right
and mid functions.

Regards
FSt1

"CarJO129" wrote:

For example:

I want column A to read "5 gal" and column B read "$25.00" and column C=A/B.
But when I enter that formula it won't compute beause in column A i have
"gal". I know an easy solution would be to add another column to read the
"gal" but I would really like to find a way to only have the 3 columns.

Any help please would be greatly appreciated. Thank you!

Click on A1 and:

Format Cells... Number Custom General" gal"

then all you have to enter in A1 is:
5
--
Gary''s Student - gsnu200909


"CarJO129" wrote:

For example:

I want column A to read "5 gal" and column B read "$25.00" and column C=A/B.
But when I enter that formula it won't compute beause in column A i have
"gal". I know an easy solution would be to add another column to read the
"gal" but I would really like to find a way to only have the 3 columns.

Any help please would be greatly appreciated. Thank you!

You can return the leading number from a cell containing a number followed
by some text using this...

LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))

So, for your formula (assuming Row 1), this should work...

C1: =LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))/B1

--
Rick (MVP - Excel)


"CarJO129" wrote in message
...
For example:

I want column A to read "5 gal" and column B read "$25.00" and column
C=A/B.
But when I enter that formula it won't compute beause in column A i have
"gal". I know an easy solution would be to add another column to read the
"gal" but I would really like to find a way to only have the 3 columns.

Any help please would be greatly appreciated. Thank you!

Custom format A to 0" gal"


Gord Dibben MS Excel MVP


On Tue, 29 Dec 2009 09:51:01 -0800, CarJO129
wrote:

For example:

I want column A to read "5 gal" and column B read "$25.00" and column C=A/B.
But when I enter that formula it won't compute beause in column A i have
"gal". I know an easy solution would be to add another column to read the
"gal" but I would really like to find a way to only have the 3 columns.

Any help please would be greatly appreciated. Thank you!