Skip to main content

I have 2 tables:



Table 1 is a list of every country in the world



Table 2 is every possible 3 letter combination of the English alphabet (all 3 character strings)



My goal is to see how many countries contain each 3-letter string.



I linked each of the 3-letter strings to every country, then performed an ARRAYJOIN():



UPPER(ARRAYJOIN(Countries))



Then I used FIND() to find all occurances of the Permutation in the Array:



FIND(Permutation,{Country Array})



The FIND field is returning way more results than expected. For example, for the Permutation “ABO” is returning 722 results. It should be returning 1 results for GABON



Can Airtable do this?



https://airtable.com/shrElPYYr5XW1Y0i2

FIND() doesn’t work that way - it is telling you that the A in “GABON” is the 722nd character in {Country Array}. You’ll want a different formula to tell you how many times “ABO” appears.



Try this:



(LEN({Country Array}) - LEN(SUBSTITUTE({Country Array}, {Permutation}, "")))/LEN({Permutation})

FIND() doesn’t work that way - it is telling you that the A in “GABON” is the 722nd character in {Country Array}. You’ll want a different formula to tell you how many times “ABO” appears.



Try this:



(LEN({Country Array}) - LEN(SUBSTITUTE({Country Array}, {Permutation}, "")))/LEN({Permutation})

Thanks! I actually came back here to post that I had figured out the solution, but you beat me to it. My code was identical to yours. Much appreciated 😀

Reply


Terms & Conditions