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

Solved
1046 1
cancel
Showing results for
Did you mean:
4 - Data Explorer

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`

1 Solution

Accepted Solutions
10 - Mercury

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})``
2 Replies 2
10 - Mercury

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})``
4 - Data Explorer

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: