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)); ""))
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)); ""))

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); ""))


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

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 ;.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!