5

What I'm trying to do is to have a formula on a record, that counts the occurrences of a specific word from within a string field on the same record that holds a paragraph of text.

If for example someone wrote in the text field: "Isn't my dog amazing! I love my dog!" .. where now an adjacent Formula field [Occurrence of word "dog"] would derive the number 2..

Is there a way to do this without Apex?

13
  • Formulas support regex, which can be used to achieve your requirement Commented Jun 4, 2015 at 16:07
  • 1
    @BrianMansfield would you mind elaborating on how a regex formula can count matches along with a working example?
    – Mark Pond
    Commented Jun 4, 2015 at 17:58
  • 1
    @MarkPond the fact that someone as reputable as you commented makes me inclined to think that it's probably not possible :P but i'll take a stab at it and see Commented Jun 4, 2015 at 18:05
  • 1
    @MarkPond I was wrong, you could enforce the number of times a known word appeared in the string with a regular expression, but I don't think you can count the number of occurrences. Sorry for the mistake Mikhail! Commented Jun 4, 2015 at 18:10
  • 1
    Well first of all, you cannot reference Long Text Areas in formulas (http://salesforce.stackexchange.com/questions/12919/salesforce-long-text-area-fields-in-formulas) So is it a field of type "Text Area (Long)" or just "Text Area" ? Commented Jun 5, 2015 at 9:24

2 Answers 2

6

Ok, so it seems to be that this is possible, but within limits, such as that your text field cannot be of the (Long) type, and must be a regular Text Area field, thus limiting the text entry field to 255 characters, and you need to know the length of the word you're looking for. (And remember to mitigate for case sensitivity by using LOWER.)

Such as, if you wish to count the occurrence of the word "dog" (a 3 character word) in your 255 character (Text Area) field, it would look like this:

(LEN(Text Area) - LEN(SUBSTITUTE(LOWER(Text Area),"dog","")))/3

*Special thanks to Julien Gaïotto for contributing.

3

That is actually a good idea to do that.

Salesforce documentation does not say if the SUBSTITUTE function is case sensitive or not... :( whereas BEGINS or CONTAINS are.

But if you're worried about case sensitivity, juste use

SUBSTITUTE(LOWER(Text Area),"dog","")
1
  • Thank you. Using LOWER will solve the last bit of reliability in the formula :) It does unfortunately state that "SUBSTITUTE" is case sensitive though, in the documentation here: help.salesforce.com/apex/… Commented Jun 5, 2015 at 15:32

You must log in to answer this question.

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