Value if cell contains text

azerty64

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

Paul Newcome

Try his:

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

James Keuning

Instead of HAS(Territoire@row; @cell)

try Territoire@row

azerty64

https://community.smartsheet.com/discussion/comment/330227#Comment_330227

Hello,

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

Toufong Vang

(1) What should happen when the value in "Territoire" is Toulouse, Pau, or something else?

(2) When the value in "Territoire" contains something like "T1-MONT" or "T3-SVT", will it always begin with "T1", "T2", "T3", "T4", etc.?

azerty64

https://community.smartsheet.com/discussion/comment/330462#Comment_330462

Hello,

(1) if the value = Toulouse then Toulouse

(2) Yes it will always start with T1, T2, T3, T4 etc.

Thank you

Paul Newcome

Try his:

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

Toufong Vang

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

Paul Newcome

@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

https://community.smartsheet.com/discussion/comment/330470#Comment_330470

Hello @Paul Newcome,

Thanks, it works

And thanks to everyone for trying to help me.

Have a good day.

Paul Newcome

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

azerty64

https://community.smartsheet.com/discussion/comment/330633#Comment_330633

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 ? 😁

Paul Newcome

@azerty64 It looks like you missed a closing parenthesis there close to the end. There should be a total of 2 before that last ;.