Formula for Character-Count

[Andrew_Enright]

Has anyone worked out how to use the COUNT function to count the number of characters in a text string?

[Kasra]

I think LEN will do what you want.

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

[Andrew_Enright]

Got it; many thanks!

A.

[Kat_Slonaker1]

What is “LEN”?
Thank you

[Andrew_Enright]

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

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

[Kat_Slonaker1]

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.

[Andrew_Enright]

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:

Airtable - Support

Guide to formula, lookup, count, and rollup fields

Formula, lookup, count, and rollup fields (columns) are powerful field types that allow you to compute a value based on values in other cells. These fields are configured in the field configuration...

[Kat_Slonaker1]

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!

[Andrew_Enright]

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.

https://airtable.com/shr9UovyUAH9r9p1U

[Peter_Todd1]

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