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

JLK · 12/22/23

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

Lucas Rayala · 12/22/23

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 · 12/22/23

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 · 12/22/23

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 · 12/22/23

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 · 12/22/23

@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 · 12/23/23

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!

Formulas and Functions

Formulas and Functions

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

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Finding if a date falls in a range in a referenced sheet

CountIF contains name from a contact list help needed

Formula for averaging days