Can I use words and numbers in the same cell in a formula?

[CarJO129]

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!

 

[Bill Sharpe]

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.

 

[FSt1]

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

 

[Gary''s Student]

Click on A1 and:

Format > Cells... > Number > Custom > General" gal"

then all you have to enter in A1 is:
5

 

[Rick Rothstein]

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

 

[Gord Dibben]

Custom format A to 0" gal"


Gord Dibben MS Excel MVP