Skip to main content

Counting Specific Letters in words

  • February 9, 2022
  • 2 replies
  • 41 views
E
Forum|alt.badge.img+3

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

    Kamille_Parks11
    Forum|alt.badge.img+27

    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", 
   ""
)
``
    E
    Forum|alt.badge.img+3
    • Evan_McMahon
    • Author
    • Participating Frequently
    • 6 replies
    • February 9, 2022

    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", 
   ""
)
``

    Thank you for your response!

    One more question -

    Is there a way to filter out words that have less than 3 vowels?

    I know I could manually make filters and new views…but that would be a ton of views and they’d be separated.

    Essentially I’d want all 3+ vowel words in 1 view.


    Terms & ConditionsAccessibility statement