Want to count every occurrence of a string within ONE CELL

[91709jack]

HI all,

Here's what the string would look like (very simplified version):

1701191000m0 1612021227n3 1611111356f0 1609251007c0 1608141700f0 1512251000n0

I would want to know the TOTAL number of times "16???? " shows up, which would be 4.

The functions I've tried so far only count ONE instance, so it would only =1.

Stumped! Appreciate your help.

THANKS!

Jack

 

[StephenCrump]

Welcome to the Forum!

A1: 1701191000m0 1612021227n3 1611111356f0 1609251007c0 1608141700f0 1512251000n0
A2: 16

Formula: =(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)

 

[91709jack]

Welcome to the Forum!

A1: A1: 1701191000m0 1612021227n3 1611111356f0 1609251007c0 1608141700f0 1512251000n0
A2: 16

Formula: =(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)

Hey thanks - BUT (my mistake forgot this ...)

A1: 1701191000m0 1612021216n3 1611111316f0 1609251007c0 1608141700f0 1512251000n0

A2 cannot be simply 16, because it would count the two RED instances as well, which it shouldn't. I'm basically wanting a count of all substrings that have THIS format: "16$$$$1$$$$$ "

 

[Aladin Akyurek]

Amend the suggestion as follows...

=(LEN("|"&A1)-LEN(SUBSTITUTE("|"&SUBSTITUTE(A1," ","|"),"|"&A2,"")))/LEN("|"&A2)

 

[Rick Rothstein]

Hey thanks - BUT (my mistake forgot this ...)

A1: 1701191000m0 1612021216n3 1611111316f0 1609251007c0 1608141700f0 1512251000n0

A2 cannot be simply 16, because it would count the two RED instances as well, which it shouldn't. I'm basically wanting a count of all substrings that have THIS format: "16$$$$1$$$$$ "

Does this do what you want...

=(LEN(" "&A1)-LEN(SUBSTITUTE(" "&A1," 16","")))/3

 

[StephenCrump]

I suspect Aladin's modification will do what you're looking for. But first, can we check:

1. In the two examples you've posted, the 7th character in each block of 12 is "1" in all cases. Can we assume this will always apply, or do we need to check?

2. Can we asssume this is not a valid match to your pattern: 1701191160m0 1512021216n3
i.e. the "16" should be at the start of a block?

 

[91709jack]

Here's an actual string:

1701181532x3 16thank* 1611071617n1 16prevm* mbe wot 1610031111x1 16jtkids* 1608161802n2 16cpros* 1606131717n1 16p60l* 1604181432n1 16q5* 1602231706n2 16ja* 1510031816f6 q4* mayor irvine knock 22k doors 1507081544n3 q3v2* 1504281111f2 vvn sd sell q2x11b 1501211515x3 omyr 1408181933n0 a4* cbnn not selling yet 1310121616n0 dog

There are TWO things I want to count:
1. how many substrings look like this: "16????1????? "
2. how many substrings look like this: "16????1???x? "

For situation 1: your formula gives me 13, but should be 6 (in BLUE). It includes the instance of 16 when it's part of 16thank*, 16prevm* - yes they have "16" but not the format of "16????1????? " If you're curious, that long string is how I date stamp my entries, while the shorter one is the name of the flyer for that visit.

For the additional situation 2, with the "x", there should be 2.

 

[StephenCrump]

So again, can we assume the "16" must be at the start of a block, i.e. preceded by a space, or first characters in cell?

For the additional situation 2, with the "x", there should be 2.

Why 2? I see only one.

 

[91709jack]

Oops. You're right, only ONE.

And yes, the blocks as I defined.

 

[RoryA]

I think this works:

=COUNT(SEARCH(B1,MID(" "&A1,IF(MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ",ROW(INDIRECT("1:"&LEN(A1)))+1),LEN(B1))))

array-entered.