How do you calculate when there is text with the number in the cell
example:
a1 value is 26oz
the 26 oz is in the cell
a2 value is $9.50 (cost, only $9.50 is in the cell)
a3 is cost per ounce.
Calculation with number and text in cell



Re: Calculation with number and text in cell
Hi,
Put this formula in A3: = A2/SUBSTITUTE(A1,"oz","")
Best Regards

Re: Calculation with number and text in cell
i got the same error message
I am thinking that you can not use text in a cell when using a calculation
Instead of referencing the cell a1 i will use 26. 
Re: Calculation with number and text in cell
The formula works fine for me.
What error message are you getting?
Best practice is to keep numbers and text in separate cells, i.e.: A1 = 26 , B1 = oz

Re: Calculation with number and text in cell
What do you have in cell A1? 26oz or 26 oz?
Maybe = A2/SUBSTITUTE(A1,"oz ","")
Best Regards


Re: Calculation with number and text in cell
26 oz is in cell

Re: Calculation with number and text in cell
ok it did work , sorry about that
now this is what I am trying to do
a1 is 750 ml
a2 is $9.50 (cost of bottle)
a3 is cost per ml
do you have a formula for that one? 
Re: Calculation with number and text in cell
Hi
You need to get the text striped from the cell
go "data" select "text to column" the rest is simple 
Re: Calculation with number and text in cell
I agree with Jim  text to columns, then you can use a simple formula.
As I said in my post  keep numbers and text in seperate columns when possible.
if you are getting your data as an alphanumeric string, then doing a text to columns is the fastest way to break them apart so that you can use the numbers. 
Re: Calculation with number and text in cell
This one should be:
= A2/SUBSTITUTE(A1,"ml ","")
but to keep things simple stick with the advice of Jim & AAE! 

Re: Calculation with number and text in cell
If your values will always be like 26 oz or 750 ml, where there is a space character between the last numeric and first alpha character, then try:
=A2/LEFT(A1,FIND(" ",A1,1)1)
It would best to provide examples of all scenarios.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!