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
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …