I think LEN will do what you want.

COUNT counts the numeric items passed to it, e.g. COUNT(1, "hello", 5) returns 2.

What is “LEN”?
Thank you

LEN(string): Returns the length of a string.

Thank you for the quick response. Useful, but it will not do what I would like to do - which is make a count of a list of states (2 letter codes) in another field.

There are a few ways to tackle this, but they depend on where your two-letter state values are.

Are they comma-separated in one field? Or are they separate fields in one record? Or are you trying to count fields in multiple records?

In any case, Rollup might be ur solve:

Thank you for persevering on this - it is good to check that I am not missing something which ought to be blindingly obvious! I do use roll-up for a different field and for that one it works a treat - but is not suitable for this one.

The data in the field in question is entered as space separated, 2-letter standard postal state codes in a single text field. In this particular case that is the easiest way to handle the information everywhere else I use it.

At least I learned what “LEN” stands for!

Thank you for persevering on this - it is good to check that I am not missing something which ought to be blindingly obvious! I do use roll-up for a different field and for that one it works a treat - but is not suitable for this one.

The data in the field in question is entered as space separated, 2-letter standard postal state codes in a single text field. In this particular case that is the easiest way to handle the information everywhere else I use it.

At least I learned what “LEN” stands for!

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,
Pete

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,
Pete

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!

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! :slightly_smiling_face:

@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! :slightly_smiling_face:

@Diogo_Nogueira could you share your 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