Formula help

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

• ✭✭✭

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"

• ✭✭✭
edited 08/23/24

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` 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.

• ✭✭✭✭✭✭
edited 08/23/24

FYI, Smartsheet currently does not support REGEX.

• ✭✭✭✭✭✭

@MarieM Here is a method that does not require helper columns:

=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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!