HELP - Contains IF

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

image.png


Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    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 ✭✭

    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...


    image.png

    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 ✭✭✭✭

    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!