Extract a number from text and multiply it to another column number
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
-
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
-
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!
-
Thank you so much, that worked perfectly!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!