Value if cell contains text

Hello everyone,

I'm french so sorry for my english...

I want to return the city name if the "Territory" column contains T1 or T2... Knowing that I use a correspondence sheet: T1 = PAU; T2 = Toulouse; ....

=IF(ISBLANK(Territoire@row); ""; JOIN(COLLECT({City name Plage 3}; {Correspondance Territoire Plage 4}; HAS(Territoire@row; @cell)); "-"))

My current formula only works if the searched value is exact (T1) but as you can see, this is not always the case (T1 - Mont).


Can you help me please

Thank you Google Translate..

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 05/20/22 Answer ✓

    Try his:


    =IF(ISBLANK(Territoire@row); ""; JOIN(COLLECT({City name Plage 3}; {Correspondance Territoire Plage 4}; @cell = LEFT(Territoire@row; FIND(" "; Territoire@row) - 1)); "-"))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!