How to count the number of times a substring appears within a string?

Hi all,

I have a column that tracks activity, and is fairly messy since it comes directly from a database.

here is a sample of some of the cell contents from the column "Track" (each line below represents a cell):

{sent_first_weekly_digest": "2023-12-08 03:10:40"

{skip_reasons": ["1"], ["2"]

{skip_reasons": ["0", sent_first_weekly_digest": "2023-12-08 03:10:40"

What I'm trying to do is count the number of times each skip reason appears. I'm struggling because the formulas I have tried, don't seem to like the ["1", either because of the brackets or because of the "" marks, so they aren't returning the results I want. I can't use the number not joined with the quotes though, because then it will return two 1s and three 2s for the first cell from the date/time.

Here are the two formulas I have tried, both resulting in #unparsable:

=COUNTIF(Track@row, CONTAINS("1", Track@row))

= (LEN([Track]@row) - LEN(SUBSTITUTE([Track]@row, "["1"", ""))) / LEN("["1"")

Any ideas for how I might tackle this?

  • Jennie


Best Answer

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    Hi @JLK, you can reference a quote using the special character CHAR(34). This formula:

    =COUNTIF(Track@row, CONTAINS("1", Track@row))

    becomes this formula (you also need to use the @cell reference within the criteria):

    =COUNTIF(Track@row, CONTAINS(CHAR(34)+1+CHAR(34), @cell))

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    Hi, @JLK, use the backslash \ to escape the double quote mark, e.g., \". So the expression that includes the double quote marks for "1" is "\"1\"". Your CONTAINS() expression would be:

    CONTAINS("\"1\"", Task@row)

    The COUNTIF() formula for the Task column would be:

    =COUNTIF(Task:Task, CONTAINS("\"1\"", @cell)) 
    
  • JLK
    JLK ✭✭

    Hmm... I'm still getting # UNPARSABLE

    Just so I'm clear, the \ basically works like brackets in that it is telling the forumula to read only what is inside the \, correct?

    Also, in this example, what does the @cell do in this case, does it mean we are looking at whatever cell it happens to be on in the range Task:Task?


    Thanks,

    • Jennie
  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    Hi @JLK, you can reference a quote using the special character CHAR(34). This formula:

    =COUNTIF(Track@row, CONTAINS("1", Track@row))

    becomes this formula (you also need to use the @cell reference within the criteria):

    =COUNTIF(Track@row, CONTAINS(CHAR(34)+1+CHAR(34), @cell))

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    @JLK, change it the range from "Task:Task" to "Track:Track". I misread the column name.

    The escape character \ means that what follows is to be used literally instead of the closing mark for a string/text. In this case, the string is ""1"".

    With regard to @cell, think of it as what the function is doing when looking up-and-down the column, Track:Track. As the function iterates through each cell in the column, you want it to look at (or evaluate) the current cell, "@cell". In this case, whether or not the cell contains "1".

  • JLK
    JLK ✭✭

    Thank you for the clarification Toufong! I still couldn't get the \ to work, but the (CHAR(34) + 1 (CHAR(34) version of the formula that Lucas provided worked! Thank you both!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!