Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# Counting Specific Letters in words

373 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hi Everyone,

I have a database of 10k+ words.

My goal is to count how many times a letter appears in each of the words.

For example: Apples contains 1 (a) 2 (p) 1 (L) and 1 (e) 1 (s)

My original idea was to create 26 fields, 1 for each English letter. I can’t seem to figure out a formula that can do this.

Secondly I’d like to then have a table that is able to take the most common letters and display the most common words of various lengths that already exist within my database.

Is this possible?

2 Replies 2
16 - Uranus

To count how many times a letter appears in a string of text, both lowercase and capital case, you could replace all instances of the letter with “nothing” and find the difference in length of the modified text string and the original. That would look like:

``````(LEN({Field Name}) - LEN(SUBSTITUTE(LOWER({Field Name}), "a", ""))) & " (a)"
``````

^ you could have a single formula field that covers all letters by following this pattern:

``````TRIM(
(LEN({Field Name}) - LEN(SUBSTITUTE(LOWER({Field Name}), "a", ""))) & " (a) " &
(LEN({Field Name}) - LEN(SUBSTITUTE(LOWER({Field Name}), "b", ""))) & " (b) " &
(LEN({Field Name}) - LEN(SUBSTITUTE(LOWER({Field Name}), "c", ""))) & " (c) " &
...
)
``````

Wrapping everything in TRIM() removes extra spaces at the front/end of the result. If you don’t want to list letters that don’t appear, you can replace all "0 (x) " by doing

``````REGEX_REPLACE(
[formula above],
"0\\s\\([[:alpha:]]\\)\\s",
""
)
````````
4 - Data Explorer