Extract a number from text and multiply it to another column number

Options

Hi

A spreadsheet I am using that I have imported into Smartsheet has this formula:

=IFERROR(W63*VALUE(RIGHT(X63, LEN(X63)-FIND("=", X63)))," ")

I managed to get it to work using it like this in Smartsheet

=IFERROR([System Details - PV Size (kWp) - QTY]@row * VALUE(RIGHT([System Details - PV Size- Model (kWp)]@row, LEN([System Details - PV Size- Model (kWp)]@row) - FIND("0.", [System Details - PV Size- Model (kWp)]@row))), " ")

I managed to get it to work using the "0." rather than the "=" as it kept coming blank.

My issue comes is that the next few columns that I need to use the formula doesnt have the 0. and now I am unable to find a way to make it work.

Would someone be able to assist?

Thank you

Melissa

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Mel Collis,

    I think this should help do what you're after:

    =IFERROR(VALUE(RIGHT([System Details - PV Size- Model (kWp)]@row, LEN([System Details - PV Size- Model (kWp)]@row) - FIND("=", [System Details - PV Size- Model (kWp)]@row) - 1)) * [System Details - PV Size (kWp) - QTY]@row, "")

    Sample output:

    Hope this helps, but if there are any problems/questions, just let us know!

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Mel Collis,

    I think this should help do what you're after:

    =IFERROR(VALUE(RIGHT([System Details - PV Size- Model (kWp)]@row, LEN([System Details - PV Size- Model (kWp)]@row) - FIND("=", [System Details - PV Size- Model (kWp)]@row) - 1)) * [System Details - PV Size (kWp) - QTY]@row, "")

    Sample output:

    Hope this helps, but if there are any problems/questions, just let us know!

  • Mel Collis
    Options

    Thank you so much, that worked perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!