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


    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


    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.

    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!

Register Yourself Login