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": "20231208 03:10:40"
{skip_reasons": ["1"], ["2"]
{skip_reasons": ["0", sent_first_weekly_digest": "20231208 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

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

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))

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

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))

@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 upanddown 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".

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
Categories
Check out the Formula Handbook template!