IF FIND RIGHT String

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,

Find more posts tagged with

Sheets

Formulas

Project Management

Comments

Mike Wilday

I tested it out... the Find function is returning a value of where it finds the word you are looking for. If you add the >0 to your IF statement after find you will be able to get the results you are looking for. Try this...

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

The Find formula returns the position that it finds the characters you are looking for. Because you were finding the leftmost characters with a value of 0 in your original formula I imagined it gave you the results you were looking for.

https://help.smartsheet.com/function/find

lumorrow

Thanks Mike,

I tried to use this with all of the statuses and it didn't work. I almost wonder if Smartsheet has a maximum amount of values for formulas? I pasted the beast below!

=IF(FIND("CON - 0 - In SOW", Status2)>0, "In SOW", IF(FIND("CON - 0 - Not In SOW", Status2)>0, "Not In SOW", IF(FIND(“CON - 1 - TEST File Preparation", Status2)>0, "TEST File Preparation", IF(FIND(“CON - 2 - TEST Conversion", Status2)>0, "TEST Conversion", IF(FIND(“CON - 3 - TEST Validation", Status2)>0, "TEST Validation", IF(FIND(“CON - 4 - PROD File Preparation", Status2)>0, "PROD File Preparation", IF(FIND(“CON - 5 - PROD Conversion", Status2)>0, "PROD Conversion", IF(FIND(“CON - 6 - PROD Validation", Status2)>0, "PROD Validation", IF(FIND(“CON - 7 - Completed", Status2)>0, "Completed", IF(FIND(“CON - 7 - Not Approved", Status2)>0, "Not Approved", IF(FIND(“CS - Closed", Status2)>0, "Closed", IF(FIND(“CS - In Development", Status2)>0, "In Development", IF(FIND(“CS - In Progress", Status2)>0, "In Progress", IF(FIND(“CS - On Hold", Status2)>0, "On Hold", IF(FIND(“CS - Open", Status2)>0, "Open", IF(FIND(“CS - Resolved", Status2)>0, "Resolved", IF(FIND(“CS - Waiting on Customer", Status2)>0, "Waiting on Customer", IF(FIND(“CS - Waiting on Third Party", Status2)>0, "Waiting on Third Party", IF(FIND(“FUNC - 0 - In SOW", Status2)>0, "In SOW", IF(FIND(“FUNC - 0 - Not in SOW", Status2)>0, "Not in SOW", IF(FIND(“FUNC - 1 - Kick Off", Status2)>0, "Kick Off", IF(FIND(“FUNC - 2 - Workshop", Status2)>0, "Workshop", IF(FIND(“FUNC - 3 - Prototype", Status2)>0, "Prototype", IF(FIND(“FUNC - 4 - Acceptance", Status2)>0, "Acceptance", IF(FIND(“FUNC - 5 - Go Live", Status2)>0, "Go Live", IF(FIND(“FUNC - 6 - Post Go Live", Status2)>0, "Post Go Live", IF(FIND(“FUNC - 7 - Closed", Status2)>0, "Closed", IF(FIND(“INT - 0 - In SOW", Status2)>0, "In SOW", IF(FIND(“INT - 0 - Not in SOW", Status2)>0, "Not in SOW", IF(FIND(“INT - 1 - Requirement", Status2)>0, "Requirement", IF(FIND(“INT - 2 - Development", Status2)>0, "Development", IF(FIND(“INT - 3 - Functional Testing", Status2)>0, "Functional Testing", IF(FIND(“INT - 4 - Customer Testing", Status2)>0, "Customer Testing", IF(FIND(“INT - 5 - Vendor Testing", Status2)>0, "Vendor Testing", IF(FIND(“INT - 6 - Production Migration", Status2)>0, "Production Migration", IF(FIND(“INT - 7 - Production Support", Status2)>0, "Production Support", IF(FIND(“INT - 8 - Completed", Status2)>0, "Completed", IF(FIND(“INT - 8 - Not Approved", Status2)>0, "Not Approved", IF(FIND(“FF - 0 - In SOW", Status2)>0, "In SOW", IF(FIND(“FF - 0 - Not in SOW", Status2)>0, "Not in SOW", IF(FIND(“FF - 1 - Requirement", Status2)>0, "Requirement", IF(FIND(“FF - 2 - Development", Status2)>0, "Development")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Mike Wilday

What error were you getting?

You had some extra ending parenthesis in there that would have caused an issue. I always paste my formulas into Notepad++ which will highlight your open and closing parentheses. Also, you can delete all the closing parentheses at the end of your formula and Smartsheet will add them for you.

Try this one.

lumorrow

Thanks for the Notepad++ tip I just installed, however I'm still getting #UNPARSEABLE as my error message. I'm not sure if maybe I should go another route in identifying the value. Is there a nested Right function that I could try instead?

Mike Wilday

I can see another major issue as well.. some of your quotes are smart quotes, usually caused by word processors. Look carefully at the text and you'll see the slanted quotes on some of them. Copy and paste that into Notepad++ and then replace all quotes with " straight quotes. That might also be the issue.

lumorrow

OMG Mike! I literally just screamed! (good thing I'm home) lol THANK YOU SOOOOO MUCH! You have no idea!

All the Best,

Mike Wilday

Was that the issue?

Sounds like you got it to work! Glad I could help you out!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…