IF FIND RIGHT String

lumorrow
lumorrow ✭
edited 12/09/19 inFormulas and Functions

Hello,

I’ve been searching all over the Smartsheet community since last week and was hoping someone could offer some assistance. I have a long Status value that I need to auto populate a shorter value in another column. I really just need a contains formula but I haven't seen that as an option here.

I could do this for the first half of the status because that was the beginning of the status (LEFT). But for the second half I’m having a lot of trouble as it begins on the Right and not the Left.

My formula for the Left status is:

=IF(FIND("FUNC", Status2), "Functional", IF(FIND("FS", Status2), "Fast Formula", IF(FIND("CS", Status2), "Continuation Services", IF(FIND("REP", Status2), "Report", IF(FIND("CON", Status2), "Conversion", IF(FIND("INT", Status2), "Interface"))))))

Some of the status values are:

FUNC - 5 - Go Live

FUNC - 3 - Prototype

CON - 7 - Completed

CS – Closed

I need it to give me the Right part string after the number and hyphen ie:

Go Live

Prototype

Completed

Closed

I tried using the same algorithm as the Left formula but its UNPARSEABLE.

=IF(FIND("Go Live", Status2), "Go Live", IF(FIND("Prototype", Status2), "Prototype", IF(FIND(“Completed", Status2), "Completed", IF(FIND("Closed", Status2), "Closed"))))

Any help or guidance would be greatly appreciated!

Thanks,

Lu

Tags:
· Share on Facebook Share on Twitter

Comments

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
You would need to leverage the Created Date column and a formula that will evaluate the time and add 1 to the date.<\/p>

=DATEONLY(Created@row) + IF(AND(FIND(\"P\", Created@row)> 0, VALUE(MID(Created@row, FIND(\" \", Created@row) + 1, FIND(\":\", Created@row) - (FIND(\" \", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(\" \", Created@row) + 1, FIND(\":\", Created@row) - (FIND(\" \", Created@row) + 1))) < 12), 1, 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":227,"urlcode":"api-and-developers","name":"API and Developers"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":277,"urlcode":"Integrations","name":"Integrations"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":369,"urlcode":"bridge-by-smartsheet","name":"Bridge"}]},{"discussionID":108222,"type":"question","name":"Parent Status based on Children Statuses - Need help refining this formula","excerpt":"Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders vision. Here's the formula: =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), \"Complete\"), \"Complete\", IF(COUNT(CHILDREN()) = AND(COUNTIF(CHILDREN(),…","snippet":"Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders…","categoryID":322,"dateInserted":"2023-07-27T20:58:59+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T15:08:32+00:00","insertUserID":146258,"insertUser":{"userID":146258,"name":"dhall","url":"https:\/\/community.smartsheet.com\/profile\/dhall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T15:09:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T15:08:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":33,"score":null,"hot":3381050851,"url":"https:\/\/community.smartsheet.com\/discussion\/108222\/parent-status-based-on-children-statuses-need-help-refining-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108222\/parent-status-based-on-children-statuses-need-help-refining-this-formula","format":"Rich","tagIDs":[219,254],"lastPost":{"discussionID":108222,"commentID":387807,"name":"Re: Parent Status based on Children Statuses - Need help refining this formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387807#Comment_387807","dateInserted":"2023-07-28T15:08:32+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T15:08:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/P3FFJGV2II8T\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T12:21:14+00:00","dateAnswered":"2023-07-28T00:26:52+00:00","acceptedAnswers":[{"commentID":387717,"body":"

Give this a try:<\/p>

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Canceled\"), \"Canceled\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = \"Complete\", @cell = \"Canceled\", @cell = \"On Hold\")), \"Complete\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Not Started\"), \"Not Started\", IF(COUNTIFS(CHLDREN(), @cell = \"Upcoming\")> 0, \"Upcoming\", \"In Progress\"))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108217,"type":"question","name":"\"Review status\" based on multiple checkboxes","excerpt":"Hello! I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked off. The boxes that are checked off would be based off on the frequency column which is semi-annually, annually, bi-weekly, daily, quarterly,…","snippet":"Hello! I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked…","categoryID":322,"dateInserted":"2023-07-27T20:20:30+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T15:16:42+00:00","insertUserID":164178,"insertUser":{"userID":164178,"name":"cware10","url":"https:\/\/community.smartsheet.com\/profile\/cware10","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AmDrR3_JMno!ejC0B2rXGOU!0QaX9eQMbnZ","dateLastActive":"2023-07-28T15:16:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164178,"lastUser":{"userID":164178,"name":"cware10","url":"https:\/\/community.smartsheet.com\/profile\/cware10","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AmDrR3_JMno!ejC0B2rXGOU!0QaX9eQMbnZ","dateLastActive":"2023-07-28T15:16:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":32,"score":null,"hot":3381049032,"url":"https:\/\/community.smartsheet.com\/discussion\/108217\/review-status-based-on-multiple-checkboxes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108217\/review-status-based-on-multiple-checkboxes","format":"Rich","tagIDs":[219,238,254,440],"lastPost":{"discussionID":108217,"commentID":387813,"name":"Re: \"Review status\" based on multiple checkboxes","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387813#Comment_387813","dateInserted":"2023-07-28T15:16:42+00:00","insertUserID":164178,"insertUser":{"userID":164178,"name":"cware10","url":"https:\/\/community.smartsheet.com\/profile\/cware10","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AmDrR3_JMno!ejC0B2rXGOU!0QaX9eQMbnZ","dateLastActive":"2023-07-28T15:16:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/68S9XTNSOICR\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T15:15:59+00:00","dateAnswered":"2023-07-28T15:06:10+00:00","acceptedAnswers":[{"commentID":387806,"body":"

My suggestion would be to use an IF\/AND\/COUNTIFS combo for all of them then.<\/p>

COUNTIFS(Jan@row:Dec@row, @cell = 1)<\/p>


<\/p>

The above will tell you how many boxes are checked. You can combine this in an IF statement with an AND function to say that IF the Frequency@row = \"x\" AND the COUNTIFS() = #, \"Green\".<\/p>


<\/p>

=IF(AND(Frequency@row = \"Monthly\", COUNTIFS(Jan@row:Dec@row, @cell = 1) = 12)<\/strong>, \"Green\", \"Red\")<\/p>


<\/p>

Then we can write out multiple AND functions using the same logic (frequency and count) and combine them with an OR function.<\/p>

=IF(OR(AND(.....), AND(.....), AND(.....)), \"Green\", \"Red\")<\/p>


<\/p>

You should be able to use copy\/paste to maintain consistency in your AND functions and just update the actual frequency and required count for each.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":238,"urlcode":"Finance","name":"Finance"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Change Date column if new row is added after hours

    你好,我们有一个简单的系统,如果一个ti的机票cket request from a form comes in after 4:00pm, we don't start the ticket until the next day. Case: Form Entry happens at 2:30pm on 07/27/23. Start Date column should read 07/27/23. Form Entry happens at 4:30pm on 07/27/23. Start Date column should read 07/28/23. So we want…

  • Parent Status based on Children Statuses - Need help refining this formula

    Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders vision. Here's the formula: =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = AND(COUNTIF(CHILDREN(),…

  • "Review status" based on multiple checkboxes

    Hello! I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked off. The boxes that are checked off would be based off on the frequency column which is semi-annually, annually, bi-weekly, daily, quarterly,…