2

I have a text area on a form, and also a forumla field that displays the max number of words allowed to be enetered in the the text area.

I want to use a formula as a validation rule, that says if the word count is higher than the max then display an error.

Looking at the standard functions I can see there is not a word count function.

One thing that I did consider was counting using a regex for Character Space Character (a space normally separates to words).

Has anyone ever attempted a word count formula?

1
  • The REGEX formula function will only return true if it matches; it won't return a count
    – cropredy
    Feb 14, 2015 at 1:34

2 Answers 2

6

You can combine available functions to come up with the following formula which returns a number being the number of words in the field:

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

Here we are counting the number of words in the Name field.

A breakdown of how this works:

  1. Get the length of the string removing any spaces at the start or end: LEN(TRIM( Name ))
  2. Get the length of the string without any spaces at all: LEN(SUBSTITUTE( Name ," ",""))
  3. Subtract the length of the string without spaces from the length of the string without spaces at the start or end and add 1: LEN(TRIM( Name ))-LEN(SUBSTITUTE( Name ," ",""))+1
1
  • 2
    It may be worth adding substitutions for other whitespace characters, such as "\n" or "\t".
    – Adrian Larson
    May 20, 2015 at 17:38
3

Here's another solution using REGEX that handles multiple spaces between words:

NOT(REGEX(TRIM(Name),'^(\\S+\\s+){n-1,m-1}\\S+'))

For an interval of n to m words inclusive, you would modify the repeater to be {n-1, m-1}... e.g. if you wanted 3 to 5 words, it would be NOT(REGEX(TRIM(Name),'^(\\S+\\s+){2,4}\\S+'))

We want the entire expression to evaluate to true (to throw the error) - so for NOT(REGEX(A,B)) we want string A to match expression B if A is a valid input.

Patterns:

TRIM(Name) -> eliminate initial and ending whitespace from text area string
\S+        -> finds a 'word,' defined here by any character except whitespace, one or more times 
\s+        -> followed by one or more whitespace characters
{n-1,m-1}     -> repeat this n-1 to m-1 times
\S+        -> include the last word
2
  • Thanks for your reply to this! I have already implemented the other solution provided by @MarkX as this was sometime ago, however this solution with the regex would calculate the word count better! May 26, 2015 at 8:15
  • I was looking for a solution if some one added more than one space between two words, and this one is better version. Thanks @Brian
    – C0DEPirate
    Jul 2, 2020 at 5:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .