Formula help

System

This discussion was created from comments split from: Formula to pull constant number string from variable text strings.

MarieM

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"

MarieM

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

jmyzk_cloudsmart_jp

Hi @MarieM

Here is a solution that utilizes the relative position of each status value.

https://app.smartsheet.com/b/publish?EQBCT=2a8b3cb7859d4845af07e23ec08c3e09 (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]#))

smartsheetrr

To extract 9-digit numbers from text in Smartsheet, use a helper column to clean the text with SUBSTITUTE or REGEX. Then, use the FIND or CONTAINS function to search for 9-digit patterns. Since Smartsheet doesn't have Excel's LET or TEXTSPLIT, 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.

jmyzk_cloudsmart_jp

FYI, Smartsheet currently does not support REGEX.

Paul Newcome

@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))

MarieM

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"

Paul Newcome

My apologies. I misread it. To get the first status is much less complicated.

=LEFT([Review Action]@row, FIND(" ", [Review Action]@row) - 1)

MarieM

https://community.smartsheet.com/discussion/comment/450937#Comment_450937

Thanks so much! You're always an amazing help!

Paul Newcome

Happy to help. 👍️

MarieM

https://community.smartsheet.com/discussion/comment/451339#Comment_451339

@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!

Paul Newcome

@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(……………………..

MarieM

https://community.smartsheet.com/discussion/comment/451439#Comment_451439

that worked. thanks again!

Paul Newcome

Happy to help. 👍️