Count certain characters in one cell

[Flashmus]

Hey!

Would like some help with counting certain characters in a one cell.

3312221133111

1. Would like to count certain character combination in the string above. The combination I would like to search for is how many times "11" appears.
For me is should be 3 times. First 11 then the 111 contains "11" two times.

"11" = 3 times
"22" = 2 times
"33" = 2 times

2. Would also try to count occurrences of "1". It should count just one time even tho its a single(1), double(11) or tripple(111) and so on.
Here I would like it to count 3 in the string above.

1 = 3 times
2 = 1 time
3 = 2 times

 

[ISY]

Hi

=LET(a,DIVIDI.TESTO(TRIM(A1),," "),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)="11","-"),"1","",1))))
=LET(a,DIVIDI.TESTO(TRIM(A1),," "),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)="22","-"),"2","",1))))
=LET(a,DIVIDI.TESTO(TRIM(A1),," "),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)="33","-"),"3","",1))))

 

[Flashmus]

Hi

=LET(a,DIVIDI.TESTO(TRIM(A1),," "),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)="11","-"),"1","",1))))
=LET(a,DIVIDI.TESTO(TRIM(A1),," "),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)="22","-"),"2","",1))))
=LET(a,DIVIDI.TESTO(TRIM(A1),," "),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)="33","-"),"3","",1))))

I doesnt seem to work.
I'm not sure if the DIVIDE.TESTO means TEXTSPLIT in English and I'm trying to translate it to Swedish DELATEXT
I'm getting the value of 1 when I should have the value 3

 

[ISY]

Hi

=LET(a,TEXTSPLIT(TRIM(A1),," "),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)="11","-"),"1","",1))))

 

[ISY]

Hi

Vba Mode

Sub Macro1()
Cells(10, 2).Formula = "=LET(a,TEXTSPLIT(TRIM(F4),,"" ""),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)=""11"",""-""),""1"","""",1))))"
End Sub

 

[Flashmus]

Hi

=LET(a,TEXTSPLIT(TRIM(A1),," "),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)="11","-"),"1","",1)))

Hi

Vba Mode

Sub Macro1()
Cells(10, 2).Formula = "=LET(a,TEXTSPLIT(TRIM(F4),,"" ""),LEN(CONCAT(SUBSTITUTE(FILTER(a,LEFT(a,2)=""11"",""-""),""1"","""",1))))"
End Sub

I cant this too work, im not sure if its a language issue with the textsplit command

 

[Phuoc]

Try this

=SUMPRODUCT((MID(A1;SEQUENCE(LEN(A1));1)="1")*(MID(A1&" ";SEQUENCE(LEN(A1))+1;1)<>"1"))

 

[Phuoc]

1. Would like to count certain character combination in the string above. The combination I would like to search for is how many times "11" appears.
For me is should be 3 times. First 11 then the 111 contains "11" two times.
"11" = 3 times
"22" = 2 times
"33" = 2 times

For 1.

=SUMPRODUCT((MID(A1;SEQUENCE(LEN(A1));2)="11")*1)

 

[Flashmus]

For 1.

=SUMPRODUCT((MID(A1;SEQUENCE(LEN(A1));2)="11")*1)

It worked! Im trying to get the ="11" part as cell reference instead, but failing. Have 100+ different combinations

=SUMPRODUCT((MID(A1;SEQUENCE(LEN(A1));2)="11")*1)
Any solution?

 

[Phuoc]

Change to:

=SUMPRODUCT((MID(A1;SEQUENCE(LEN(A1));2)=C1&"")*1)

Or the format in C1 is TEXT.