function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
MattMet86MattMet86 

Formula - Count occurrence of ; character

I need a formula to count the number of times a semi-colon appears in a variable. This is actually for visual flow but it is a formula like any other in Salesforce.

Any ideas? I need the count not just a TRUE or FALSE result. 

My overall goal is I need to compare the values captured from a  multi-select picklist and identify if values were either added or removed from the list. So I plan on counting the number of ; and then using a decision module to take appropriate action on another object. 

Here is an example:
09:00:25:612 FLOW_VALUE_ASSIGNMENT 3327be5ac80f0d842d9d2f061289153095dc09d-1678|varTMPriorValues|BM Web Enroll;We Care
So I need to get back 1 from this variable. 
 
09:00:25:612 FLOW_VALUE_ASSIGNMENT 3327be5ac80f0d842d9d2f061289153095dc09d-1678|varTMValues|Aflac;BM Web Enroll;We Care
And I should get 2 back from this variable. 

So I can do my decision and know that values were added to the list. 

 
MattMet86MattMet86
I don't want to do a LEN compare as this has the possibility of returning a false positive. I need to count the ; *Matt Metzinger* *Salesforce Admin / Developer* *CRM Team* *(913) 652-2087*
Deepak GulianDeepak Gulian
LEN("varTMValues|Aflac;BM Web Enroll;We Care")-LEN(SUBSTITUTE("varTMValues|Aflac;BM Web Enroll;We Care"
, ';', ''))
I think this formula might help you out.
Deepak GulianDeepak Gulian
The above formula will return you the count of ";"
MattMet86MattMet86
Sorry Deepak but that formula isn't what I am looking for. I don't want to have all the field values in the formula as there are a great many. I just need a simple function to count occurence of a specific character. 

Does anyone have a regex that can do this? 

 
Naren LarsonNaren Larson
MattMet86, replace the values with the variable name and it works (length of string including spcl char minus length of string with spcl chars removed = number of spcl chars)
Thomas GelderThomas Gelder
Thanks Naren Larson! I was wracking my brain trying to figure out how to get a count of comma separated items in a text string. Part of a whole thing of trying to convert a text string into a text collection.
Lee Gregory 4Lee Gregory 4
Thank you Deepak, your formula worked for me once I replaced the text with the variable.