If Project name column CONTAINS specific text

Okay I have 2 columns, one contains a unique number (left) and on the right that unique number is listed with the program. I have been able to accomplish this so far, however I ran into a little snag. There are some programs that contain JNEEO, and their unique TDL #. However, once I added the formula I discovered not all followed this pattern. My current formula is below, and 14 is the # of characters that contain the "prefix", which is "JNEEO TDL 101 " for example. Please note there is a space at the end to keep everything clean. Now what I need is the addition of an if statement to be something like:
If CELL X does not contain/begin with "JNEEO TDL" then use the cell value as is.
=REPLACE([Project Name (Official)]@row, 1, 14, "")
Sherry Fox
Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024
Core App Certified 🦊
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.
Best Answer
-
It's strange!
I have tested this formula at my side and it worked well.=IF(CONTAINS("JNEEO TDL", [Project Name (Official)]@row), REPLACE([Project Name (Official)]@row, 1, 14, ""), [Project Name (Official)]@row)
If it's possible, could you share your example sheet with me (thinh.huynh@giathinh.tech) so that I could look into it for you?
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech
Answers
-
Try this formula:
=IF(CONTAINS("JNEEO TDL", [Project Name (Official)]@row), REPLACE([Project Name (Official)]@row, 1, 14, ""), [Project Name (Official)]@row)
Hope it works for you.
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech -
This seems almost correct, I have circled 2 items that do NOT have "the prefix" in their name, in these instances, the entire name from the column on the left should be displayed. The rest of your formulas is correct.
Sherry Fox
Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024
Core App Certified 🦊
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.
-
Did you convert the new formula to column formula to take effect on all rows?
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech -
I just did the conversion to column formula, and there was no change.
Sherry Fox
Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024
Core App Certified 🦊
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.
-
Can you provide a screenshot with the new column formula?
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech -
Sherry Fox
Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024
Core App Certified 🦊
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.
-
It's strange!
I have tested this formula at my side and it worked well.=IF(CONTAINS("JNEEO TDL", [Project Name (Official)]@row), REPLACE([Project Name (Official)]@row, 1, 14, ""), [Project Name (Official)]@row)
If it's possible, could you share your example sheet with me (thinh.huynh@giathinh.tech) so that I could look into it for you?
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech -
PERFECT!!!!! Thanks so very much. Here is a screenshot showing the results.
Sherry Fox
Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024
Core App Certified 🦊
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.
-
Great to hear it worked for you!
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!