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

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!
Answers

Instead of
HAS(Territoire@row; @cell)
try
Territoire@row

Hello,
Thank you for your answer. But unfortunately it doesn't work

(1) What should happen when the value in "Territoire" is Toulouse, Pau, or something else?
(2) When the value in "Territoire" contains something like "T1MONT" or "T3SVT", will it always begin with "T1", "T2", "T3", "T4", etc.?

Hello,
(1) if the value = Toulouse then Toulouse
(2) Yes it will always start with T1, T2, T3, T4 etc.
Thank you

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!

Try this...
=IF(FIND(LEFT(Territoire@row,2),"T1,T2,T3,T4") = 0, Territoire@row, JOIN(COLLECT({City name Plage 3}, {Correspondance Territoire Plage 4}, @cell = LEFT(Territoire@row, FIND(" ", Territoire@row)  1)), ""))
...replaced the logical expression in @Paul Newcome's formula with
FIND(LEFT(Territoire@row,2),"T1,T2,T3,T4")=0
to check if the first two letters of the value in Territoire is in the string "T1,T2,T3,T4". 
@Toufong Vang The original post indicated that we needed to only search for the "T##", so having "T1  SVT" is throwing things off.
Your formula is going to always throw out a 0 for that first FIND function because there is not a cell containing "T1, T2, T3, T4". The FIND function throwing a 0 means it will output the same data that is already in the Territoire cell every time. This is not what the original post asked for.
The original post asked to do the search based on "T##" regardless of whatever else was in the cell. The method I used pulls the desired string to search against and accommodates other variations such as "T5" or "T98765".
@azerty64 I have also realized that I forgot to include a bit in case it is just "T1". Please see below:
=IF(ISBLANK(Territoire@row); ""; JOIN(COLLECT({City name Plage 3}; {Correspondance Territoire Plage 4}; @cell = IFERROR(LEFT(Territoire@row; FIND(" "; Territoire@row)  1); Territoire@row); ""))
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!


@azerty64 Happy to help. 👍️
Also keep in mind that there is a possibility that if the cell contains just "T1" (or any other number but no additional text), my first formula may throw an error. My last one has been updated to account for those.
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!

Thanks a lot @Paul Newcome !
But if it is indeed this formula:
=IF(ISBLANK(Territoire@row); ""; JOIN(COLLECT({City name Plage 3}; {Correspondance Territoire Plage 4}; @cell = IFERROR(LEFT(Territoire@row; FIND(" "; Territoire@row)  1); Territoire@row); ""))
I can't get it to work. Is there a mistake or is it me ? 😁

@azerty64 It looks like you missed a closing parenthesis there close to the end. There should be a total of 2 before that last ;.
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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!