HELP - Contains IF

Options
Caravan
Caravan ✭✭✭✭
edited 07/11/23 in Formulas and Functions

Hi,

Please help. Can't understand why the below not working. I'm able to figure out complex formulas but can't figure out this simple one.

Helper is a check box but I'm getting blanks. :-(

Thanks much


Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Options

    Hi Caravan, 

    I have implemented the same formula for the test, and it is working fine when the column type is ‘Text/Number’, but not for the ‘Contact List’ column type. 

    Can you please let me know which column type you are exactly using for the ‘Owner’ column, or you are using any formula for that? 

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Nandes
    Nandes ✭✭
    Options

    I have a similar problem. I have 2 columns: Project ID and Project Level. In the second column, I have the following formula: =IF(CONTAINS(".", [Project ID]@row), "L2", "L1")


    Level 1 (L1) projects are any that only have the 4 digits before the "."

    Level 2 (L2) projects are any that have digits after the "."

    As you can see, it is returning L1 on all my rows...


    The project ID field has to be a contact list type in order for the filter to work, so I can't change it to text. However, I changed it to text and it still did not work.

  • Caravan
    Caravan ✭✭✭✭
    Options

    Hi Kaveri Vipat,

    I'm able to get this working. For those that may need help with the same use case.

    Owner is the source which needs to split into Owner 1 and Owner 2. The source (Owner) can have "/", space, "," so I try to cover all bases depending how the data comes into SmartSheet (SS).

    Also, there could be 1 name or 2 names in the Owner column.

    Helper column formula

    =IF(OR(CONTAINS("/", Owner@row), CONTAINS(",", Owner@row)), 1, 0)

    Owner 1 formula

    =IF(Helper@row = 0, Owner@row, IF(Owner@row <> "", IF(FIND("/", Owner@row) > 0, LEFT(Owner@row, FIND("/", Owner@row) - 1), IF(FIND(",", Owner@row) > 0, LEFT(Owner@row, FIND(",", Owner@row) - 1)))))

    Owner 2 formula

    =IF(Helper@row = 0, " ", IF(Owner@row <> "", IF(FIND("/", Owner@row) > 0, RIGHT(Owner@row, LEN(Owner@row) - FIND("/", Owner@row)), IF(FIND(",", Owner@row) > 0, RIGHT(Owner@row, LEN(Owner@row) - FIND(",", Owner@row))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!