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,
Lu
Comments
-
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.
-
-
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.
=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"))))))))))))))))))))))))))))))))))))))))))
-
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?
-
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.
-
OMG Mike! I literally just screamed! (good thing I'm home) lol THANK YOU SOOOOO MUCH! You have no idea!
All the Best,
Lu
-
Was that the issue?
Sounds like you got it to work! Glad I could help you out!
Help Article Resources
Categories
Check out the Formula Handbook template!
