CONTAINS Formula

Options

Hello,

I need help with this formula =IF(CONTAINS([Invoice Number]@row, "Invoice"), "Yes", ""). It worked if the "Invoice Number" row value is only "Invoice", if value contains "Invoice xxx" something else after "Invoice" it doesn't put "Yes" in "Want Yes" Row.

I would like to put "Yes" in "Want Yes" row if the "Invoice Number" row contain the word "Invoice".

Thank you.

Best Answer

  • Alpha Chucky
    Alpha Chucky ✭✭✭✭
    Answer ✓
    Options

    Hello @BonW

    I know what it's like to have people add anything into the cell so you as the Smartsheet god needs a work around. You may want to use a drop down if possible to limit the variables.

    Anyway, Check out this formula:

    =IF(FIND(UPPER("Invoice"), UPPER([Primary Column]@row)) > 0, "Yes", "")


    I took the liberty of adding an additional probability and that's if your people type "invoice" using lower case. Currently, your formula only accounts for "Invoice" with a capital "I" and formula is case sensitive.

    Good luck!

    Michael

    Projects Delivered. Data Defended.

Answers

  • Alpha Chucky
    Alpha Chucky ✭✭✭✭
    Answer ✓
    Options

    Hello @BonW

    I know what it's like to have people add anything into the cell so you as the Smartsheet god needs a work around. You may want to use a drop down if possible to limit the variables.

    Anyway, Check out this formula:

    =IF(FIND(UPPER("Invoice"), UPPER([Primary Column]@row)) > 0, "Yes", "")


    I took the liberty of adding an additional probability and that's if your people type "invoice" using lower case. Currently, your formula only accounts for "Invoice" with a capital "I" and formula is case sensitive.

    Good luck!

    Michael

    Projects Delivered. Data Defended.

  • BonW
    BonW ✭✭
    Options

    It works! thank you very much Michael.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!