Formula help
Best Answer
-
Thank you @Paul Newcome !! This formula worked great to extract the middle status (vet):
=MID([Review Action]@row, FIND("/", SUBSTITUTE([Review Action]@row, ">", "/", 1)) + 1, FIND(" ", [Review Action]@row, FIND("/", SUBSTITUTE([Review Action]@row, ">", "/", 1)) + 2) - (FIND("/", SUBSTITUTE([Review Action]@row, ">", "/", 1)) + 1))
But the other formula isn't working to extract the first, admin status, in the example below, the it would be the "Reviewing"
"Reviewing - A. Smith<br/>Rejected - B. Smith<br/>Accepted - C. Smith"
Answers
-
Can someone help me write a formula for the following?
The format of my data in a cell looks like this, always following the same pattern based on the role of each reviewer:
"Reviewing - A. Smith<br/>Rejected - B. Smith<br/>Accepted - C. Smith"
First position is always the admin reviewer (here A. Smith), The 2nd position is always the Vet reviewer (here
B. Smith), and the 3rd position is always the EH&S reviewer (here
C. Smith).
The " - ", and "<br/>" are always constant. And there are always 3 reviewers.
The status for each reviewer can be 1 of the following 1) Reviewing, 2) Rejected, or 3) Accepted.
I would like to have the status of the Admin reviewer pulled over to "Admin Pre-Rev Status column and the status of the Vet reviewer pulled to the "Vet Pre-Rev Status" column
-
Hi @MarieM
Here is a solution that utilizes the relative position of each status value.
(Link to editable published demo sheet)
Reviewing =IFERROR(FIND([Opt 1]#, [Primary Column]@row), "")
Rejected =IFERROR(FIND([Opt 2]#, [Primary Column]@row), "")
Accepted =IFERROR(FIND([Opt 3]#, [Primary Column]@row), "")
MIN =MIN(Reviewing@row:Accepted@row)
MEDIAN =MEDIAN(Reviewing@row:Accepted@row)
MAX =MAX(Reviewing@row:Accepted@row)
Admin Pre-Rev Status =IF(Reviewing@row = MIN@row, [Opt 1]#, IF(Rejected@row = MIN@row, [Opt 2]#, IF(Accepted@row = MIN@row, [Opt 3]#)))
Vet Pre-Rev Status =IF(Reviewing@row = MEDIAN@row, [Opt 1]#, IF(Rejected@row = MEDIAN@row, [Opt 2]#, IF(Accepted@row = MEDIAN@row, [Opt 3]#)))
EH&S Rev Status =IF(Reviewing@row = MAX@row, [Opt 1]#, IF(Rejected@row = MAX@row, [Opt 2]#, IF(Accepted@row = MAX@row, [Opt 3]#)) -
To extract 9-digit numbers from text in Smartsheet, use a helper column to clean the text with
SUBSTITUTE
orREGEX
. Then, use theFIND
orCONTAINS
function to search for 9-digit patterns. Since Smartsheet doesn't have Excel'sLET
orTEXTSPLIT
, you may need to manually separate the text into multiple columns, and then apply formulas to identify the 9-digit numbers. It's more manual, but with some creativity, you can achieve similar results. -
FYI, Smartsheet currently does not support REGEX.
-
@MarieM Here is a method that does not require helper columns:
Admin Status:
=MID([String Column Name]@row, FIND("!", SUBSTITUTE([String Column Name]@row, ">", "!", 1)) + 1, FIND(" ", [String Column Name]@row, FIND("!", SUBSTITUTE([String Column Name]@row, ">", "!", 1)) + 2) - (FIND("!", SUBSTITUTE([String Column Name]@row, ">", "!", 1)) + 1))
Vet Status:
=MID([String Column Name]@row, FIND("!", SUBSTITUTE([String Column Name]@row, ">", "!", 2)) + 1, FIND(" ", [String Column Name]@row, FIND("!", SUBSTITUTE([String Column Name]@row, ">", "!", 2)) + 2) - (FIND("!", SUBSTITUTE([String Column Name]@row, ">", "!", 2)) + 1))
-
Thank you @Paul Newcome !! This formula worked great to extract the middle status (vet):
=MID([Review Action]@row, FIND("/", SUBSTITUTE([Review Action]@row, ">", "/", 1)) + 1, FIND(" ", [Review Action]@row, FIND("/", SUBSTITUTE([Review Action]@row, ">", "/", 1)) + 2) - (FIND("/", SUBSTITUTE([Review Action]@row, ">", "/", 1)) + 1))
But the other formula isn't working to extract the first, admin status, in the example below, the it would be the "Reviewing"
"Reviewing - A. Smith<br/>Rejected - B. Smith<br/>Accepted - C. Smith"
-
My apologies. I misread it. To get the first status is much less complicated.
=LEFT([Review Action]@row, FIND(" ", [Review Action]@row) - 1)
-
Thanks so much! You're always an amazing help!
-
Happy to help. 👍️
-
@Paul Newcome , could you please help me figure out why this formula is working on 1 row, but I get #INVALID OPERATION on the others? I'm sure it's something completely rudimentary, so I apologize in advance!
-
@MarieM I don't see anything wrong with the formula that would throw that error. Maybe a different way of writing it?
=IF([Column Name]@row <> "", [Column Name]@row + CHAR(10), "") + IF(……………………..
-
that worked. thanks again!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!