 # Calculation with number and text in cell

• 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.

• 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

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• 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

Jim
"The problem with designing vba code completely foolproof is to underestimate the ingenuity of a complete fool."

• 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.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• 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.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!