Using FIND() on an ARRAYJOIN() returning inexpected results

[James_Otey]

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

[AlliAlosa · Accepted solution]

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})

[James_Otey]

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 :grinning: