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 PreRev Status column and the status of the Vet reviewer pulled to the "Vet PreRev 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 PreRev Status =IF(Reviewing@row = MIN@row, [Opt 1]#, IF(Rejected@row = MIN@row, [Opt 2]#, IF(Accepted@row = MIN@row, [Opt 3]#)))
Vet PreRev 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 9digit numbers from text in Smartsheet, use a helper column to clean the text with
SUBSTITUTE
orREGEX
. Then, use theFIND
orCONTAINS
function to search for 9digit 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 9digit 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
 Smartsheet Customer Resources
 62.2K Get Help
 358 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!