Formula for Character-Count

Hmm. Not sure yet how to crack it for ya.

If you had the states in separate fields, it’s easy to use COUNTALL and then also make a CONCATENATE field that creates what you have now; multiple states in one field.

Hi Kat

If, for example, the name of your field is “States” with string value similar to AA AB AC AD etc this formula should work for you:

IF(LEN(States) > 0, (LEN(TRIM(States)) - LEN(SUBSTITUTE(States, " “, “”))) + 1,”")

It handles empty cells, double spaces,and leading/trailing spaces

Have Fun,


Nice! How’s this working?

Thank you, Peter - that sounds like it will fill the bill perfectly! I will have to try that after clearing out my post-holiday work pile up!

Works as desired! I give up being able to average the number of states since it is now text instead of a number, but that is an easy ‘price’ to pay. Thanks again, Peter!

Oh, no trouble at all Kat, it was a fun challenge to work out :slight_smile:

Hi guys, Do you know how could I count words, instead of characters?

@Diogo_Nogueira - I don’t know of a ‘simple’ solution, but here’s a workaround.

If NOTES is the name of the field you are trying to count words for, try:

LEN(Notes)-LEN(SUBSTITUTE(Notes," “,”"))+1

LEN(Notes) will count the total characters

SUBSTITUTE(Notes," “,”") will remove the spaces from Notes. Wrapping that in LEN will then count the characters in Notes without spaces.

Then we need to add one (1) to this, since the last word won’t have a space after it.

Be aware that if you use a space in the field you are counting, it will effectively tell this formula that there is a separate word.

Hope this helps! :grin:


Nice workaround Gareth,

To further prevent user error though, I would suggest to TRIM Notes to eliminate any leading and trailing spaces.

Kind regards,


1 Like

Thanks Gareth! I already found a solution! :grinning:

1 Like

@Diogo_Nogueira could you share your solution?

Does anyone know how to make a word count formula?

There are several threads:

Look at the reply by Gareth (above) for a solution.

@Justin_Barrett When I tried that I got an error message.

@Kaiti_Hill Looks like the quotes in the version above got mangled somehow. Some are straight, others are curled. Try copying this, which is the fixed version that I tested successfully:

LEN(Name)-LEN(SUBSTITUTE(Name," ","")) + 1

@Justin_Barrett you are WONDERFUL. thank you so much!!

So, I’m trying to come up with a way to complicate this and I believe I have found it. I have an ever changing array of characters that can be sequential but may not be (A, B, C, D, F… etc.) and I want to count the occurrence of each one in a much longer array (AAACCCBBDDIIFFIAAHDICCHAIKLA… etc.). I have my base structured such that one field (Field 1) keeps track of my changing array like above, and another field (Field 2) that keeps up with a concatenated larger array. Any one have any ideas on counting each character from Field 1 in Field 2?


To pull that off, the software needs some way to iterate through both arrays. Airtable isn’t built for any kind of iteration…yet. I’m confident that this will be possible someday, but today isn’t that day. Sorry.

1 Like

Thanks for the reply Justin. I have been racking my brain to come up with an iteration work around, without using an API (mostly because I’m not yet knowing how to write/implement an API).