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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!