Count the number of time a specific sequence shows in a field

[IJH34]

I have a field that contains number and letters.

In most cases it usually contains something like this...

Field 1

B000110012010000100123

I want to count the number of times consecutive 1's appear in my field. So the output for the field above would be 1.

I used Regex Count Matches to complete this and for the most part it worked. But it didn't work when I had a field like such...

Field 1

B011100000011110002310

For this field I would expect an output of 5, because there are 5 times that a 1 is followed by a consecutive 1. Regex Count Matches only gives me 3. 

[danilang]

Hi @IJH34 

Are you looking to count all possible sequences or only specific ones?  If specific, do you have a list or is it ad-hoc?

Dan 

[JosephSerpis · Accepted solution]

Hi @IJH34 I mocked up a workflow that produces your output. Let me know what you think?

[danilang]

A little more dynamic than @JosephSerpis but along the same lines

Dan

[Claje]

Wanted to include the RegEx solution as well, since it may be more performant on a large dataset than splitting to rows and doing multi-field matching.

REGEX_CountMatches([Field1], '1(?=1)')

This will do a  Positive Lookahead after each 1 to see if it is followed by another 1, but won't include the second "1" character in the match.  This correctly returns "1" and "5" for your two examples.

A more dynamic version of this (specified first character, repeated again) is:

REGEX_CountMatches([Field1], '(0)(?=\1{1})')

If you replace the "0" in the first set of parentheses with any character, it will dynamically update the regex to look for those paired characters in the same way.  You can also adjust the "{1}" to be any number to modify how many repeating characters you are looking for, for example "(1)(?=\1{2})" will return all cases where there are 3 repeating 1's, so "111" returns 1 match and "1111" returns 2.

Hope this helps!