# Count certain characters in one cell



## Flashmus (Jan 4, 2023)

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 (Jan 4, 2023)

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 (Jan 4, 2023)

ISY said:


> 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 (Jan 4, 2023)

Hi

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


----------



## ISY (Jan 4, 2023)

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 (Jan 4, 2023)

ISY said:


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





ISY said:


> Hi
> 
> Vba Mode
> 
> ...


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


----------



## Phuoc (Jan 4, 2023)

Try this

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


----------



## Phuoc (Jan 4, 2023)

Flashmus said:


> 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 (Jan 4, 2023)

Phuoc said:


> 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 (Jan 4, 2023)

Change to:

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

Or the format in C1 is *TEXT*.


----------



## Flashmus (Jan 4, 2023)

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


----------



## 52779 (Jan 4, 2023)

Hi,
as an alternative solution






Cell D3

```
=LET(a,MID(B2,SEQUENCE(LEN(B2)),2),b,REPT(SORT(UNIQUE(MID(B2,SEQUENCE(LEN(B2)),1))),2),HSTACK(b,MAP(b,LAMBDA(x,SUM(--(a=x))))))
```

Cell J3

```
=LET(a,MID(B2,SEQUENCE(LEN(B2)),1),b,FILTER(a,1=SCAN(1,SEQUENCE(LEN(B2)),LAMBDA(x,y,IF(y=1,x,IF(INDEX(a,y-1)=INDEX(a,y),x+1,1))))),c,SORT(UNIQUE(a)),HSTACK(c,MAP(c,LAMBDA(x,SUM(--(b=x))))))
```


----------



## Flashmus (Jan 5, 2023)

Solution found by Phouc, THX!
1. =SUMPRODUCT((MID(A1;SEQUENCE(LEN(A1));2)="11")*1)

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

Havent tried the solution by 52779


----------

