Formula for Character-Count


#1

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


#2

I think LEN will do what you want.

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


#3

Got it; many thanks!

A.


#4

What is “LEN”?
Thank you


#5

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


#6

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.


#7

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:


#8

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!


#9

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


#10

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


#11

Nice! How’s this working?


#12

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!


#13

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!


#14

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


#15

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


#16

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


#17

Nice workaround Gareth,

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

Kind regards,

Alex


#18

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