Finding text from an array in another text (not array)

Airtable Questions
1

Table A is a list of items (assets) to borrow
Table B is a list of departments that borrow products
Table C is a list of banned words

Table A is linked with Table B.
Table C is linked with Table B.

I need to prevent some items to be borrowed by some departments.

I could link table A and B, and select the blocked departments from Table A (Items) or select blocked items from Table B (Departments).

But that is too much work. (I don’t want to have to make that selection every time an item is added to the Table A).

So instead I have Table C with words (single words or multi-word expressions) that I want to block (specifically, I want to check the item title (Table A) against these words (Table C). If the item name contains an expression, it should be blocked from department.

All I need is to add another field in LM table e.g. called Blocked. So if one of the expressions in the array field can be found in the item title, then show the name of department. This field Blocked will say which department should not be able to borrow the item. I will then use this field to filter out my items I need.

Example:
Table C will list expression “projector” as a blocked item keyword.
In Table B, for the specific Department, I will pick in the linked field (to Table C) the expression “projector”.
In Table A, I have a list items e.g. in the primary field Dell projector next item is Epson projetor etc…

If I now try to select the specific Department using a linked field (linked to Table B), I want to see warning the Department is banned. It will be banned because item title includes the keyword projector.

I don’t mind I am seeing the blocked Department still as an available choice in the Linked field when picking the linked item from the Table B.

All I need is that warning telling me the Department should not be able to borrow the item.

Sorry for the long explanation, just wanted to make sure it is clear what I am trying to do.

The issue is the linked field Table B (to table C) can have multiple values. (Multiple expressions can be banned for the department). I believe it is considered an array lookup and a simple SEARCH or FIND formula with the arguments as they are for the normal fields will not work.

I tried to look at this suggestion here below… but I am confused about what is the actual solution in this thread here…?

https://community.airtable.com/t5/other-questions/how-to-find-text-in-text-string/m-p/117626#M37573

2

Nevermind, I figured it out and got my fix.

There is no native solution as Airtable can’t handle that… The reason explain here:

So I did a workaround in case somebody interested to know. It’s very ugly but workable for me because I have a finite number of values for Departments. So expressions searched come in finite number of variations. Only the text where I am searching has no limit. If both are unlimited, going this way would not be feasible.

If more rows needed, they can be easily expanded with ChatGPT.

One more thing… the text in Department may not contain commas because that is what is used as separator for Array.

IF(Department,

IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1,100))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1,100)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),300,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),500,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),500,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),700,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),700,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),900,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),900,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1300,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1500,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1500,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1700,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1700,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1900,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),1900,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2300,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2500,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2500,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2700,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2700,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2900,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),2900,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3300,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3500,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3500,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3700,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3700,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3900,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),3900,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4300,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4500,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4500,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4700,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4700,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4900,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),4900,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5300,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5500,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5500,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5700,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5700,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5900,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),5900,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6300,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6500,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6500,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6700,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6700,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6900,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),6900,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7300,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7500,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7500,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7700,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7700,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7900,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),7900,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8300,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8500,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8500,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8700,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8700,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8900,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),8900,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),9100,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),9100,200)),LOWER(CONCATENATE(Title)))>0,1,0))+
IF(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),9300,200))=BLANK(),0,	IF(FIND(TRIM(MID(SUBSTITUTE(LOWER(ARRAYJOIN(Department,", ")),", ",REPT(" ",190)),9300,200)),LOWER(CONCATENATE(Title)))>0,1,0))


,
BLANK())
3

I’m afraid that I don’t quite follow what you are trying to do, and it sounds like you found a solution anyway. However, your formula is very long and you might end up reaching the limits of Airtable formulas. (While there are no documented limits on the length of an Airtable formula, and the limit does not seem to be based on character count, people have encountered issues with very long formulas.)

My initial thoughts were to use REGEX functions to turn the value of one field into a regular expression that is then applied to the value of the second field. However, this method does require figuring out REGEX patterns, has limits on possible field values, and can be a bit tricky for people who are not familiar with regular expressions.

1 Like
4

Yes it is complicated. I like convenience and the complexity is the tax I pay for convenience.

Yes, Regex was actually proposed … LOL … by ChatGTP itself, it told me my formula is too complicated and long and it doesn’t understand what I am trying to do so it suggested me to use Regex formula instead. It even provided me a working Regex formula, no kidding.

But the issue with the Regex formula was it wasn’t catching up deviations. The long formula will simply provide blank result if the matched blocked words are e.g. mistyped or other words are used.

Heck yah, I did beat that ChatGPT to the punch on this one. :grinning: