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.

Tags:

Best Answer

  • Gia Thinh
    Gia Thinh Community Champion
    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

  • Gia Thinh
    Gia Thinh Community Champion

    @SherryFox,

    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

  • SherryFox
    SherryFox ✭✭✭✭

    @Gia Thinh ,

    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.

  • Gia Thinh
    Gia Thinh Community Champion

    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

  • SherryFox
    SherryFox ✭✭✭✭

    @Gia Thinh ,

    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.

  • Gia Thinh
    Gia Thinh Community Champion

    Can you provide a screenshot with the new column formula?

    Gia Thinh Technology - Smartsheet Solution Partner.
    Email : thinh.huynh@giathinh.tech

  • SherryFox
    SherryFox ✭✭✭✭

    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.

  • Gia Thinh
    Gia Thinh Community Champion
    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

  • SherryFox
    SherryFox ✭✭✭✭

    @Gia Thinh ,

    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.

  • Gia Thinh
    Gia Thinh Community Champion

    Great to hear it worked for you!

    Gia Thinh Technology - Smartsheet Solution Partner.
    Email : thinh.huynh@giathinh.tech

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!