0

Count the number of instances of a character in a string

Madhouse Advertising Admin

January 23, 2017 21:45

None

Follow

Hi Guys:

I have a column that we want to count the number of commas in the string.

Here is the string:

01/21/2017, 01/23/2017

Expected result :

1

 

Any help would be appreciated...

 

Thanks,

David

• Facebook
• Twitter
• LinkedIn

5 comments

• 
  Janice Janczyn January 23, 2017 22:02 Official comment

  Hi David,

  You can take advantage of the ARRAY function to separate a text string into a list of items and then wrap COUNTALL around it to count the number of items in the list, then subtract 1.
  
  ARRAY( "01/21/2017, 01/23/2017")  will return a list of 2 items

       01/21/2017

       01/23/2017

  Wrap COUNTALL around the ARRAY and subtract 1 to get the number of commas:

       COUNTALL( ARRAY( "01/21/2017, 01/23/2017")  ) - 1

  If you want to count a character other than a comma, use SUBSTITUTE to convert the character(s) to a comma, then wrap COUNTALL(ARRAY(..)) around the formula, but first SUBSTITUTE any existing commas to another string so these aren't used in separating the text string.

       COUNTALL( ARRAY( SUBSTITUTE( SUBSTITUTE( "01/21/2017, 01/23/2017", ",","*"), "/", ",")  ) )  - 1

  Thanks,

           Janice

• 0
  
  Madhouse Advertising Admin January 24, 2017 19:40

  Hi Janice:

  Thanks so much.  This makes sense.  However, I wanted to count the number of list items in each row in this column.

   

  EG:

  record 1 ) 01/21/2017, 01/23/2017     => 1

  record 2 ) 01/21/2017, 01/23/2017, 1/24/2017    => 2 

   

  Let me know if I have explained myself clearly. Sorry if I wasnt clearer

  Thanks,

  David

   

• 0
  
  Not Defined January 24, 2017 22:30

  Hi David,

  Use the MAP function to loop through multiple records:

       MAP ( records, "temp", (COUNTALL( ARRAY( $temp ) ) -1 ) )

  where 'records' points to your data and temp is a variable.

  Thanks,

           Janice

• 0
  
  Madhouse Advertising Admin January 25, 2017 18:24

  Thanks, that was exactly what I was looking for....

  You're smart!

• 0
  
  Amir Abbas Darbani October 23, 2018 19:03

  Hi,

  Here's an updated solution that takes advantage of our new LENGTH function. Using the SUBSTITUTE_REGEX function, we can match each comma surrounded by 0 or more non-comma characters (specified by the regex [^,]*,[^,]*) and substitute these with one comma (,).  Then we use the LENGTH count the commas returned by the SUBSTITUTE_REGEX. For example, 

      LENGTH( SUBSTITUTE_REGEX( ARRAY( "abc,def","ghi,jkl,mno,pqr" ),
                                                              "[^,]*,[^,]*",
                                                              ",")

  To handle the case where a string has 0 commas, we match each comma surrounded by 0 or more non-comma characters ([^,]*,[^,]*)  or (|) no commas ([^,]) and substitute these with one comma (,) or null("") respectively.

       LENGTH( SUBSTITUTE_REGEX( ARRAY( "abc,def","ghi,jkl,mno,pqr" ),
                                                               ARRAY( "[^,]*,[^,]*", "[^,]*" ),
                                                               ARRAY( ",", "" ) ) )

  SUBSTITUTE_REGEX is considered advanced functionality. There are many online references on using regex, such as regexr.com, or email support@klipfolio.com for guidance.

   

  Thank you,

  Amir