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

Mel Collis

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

Nick Korna

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!

Nick Korna

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

Thank you so much, that worked perfectly!