Forum Discussion
Occurrence in an excel serie
Hello, I have an excel column filled with 0 and 1. I would like to count the number of successive 1 and 0 Ex: 011001011100000 The result could be something like 2 columns, I. E the first for 0 an...
davidleal oh yea, using textsplit is a good idea. I did it different than you and allow more flexible options and error checking (in case of inputs like 111111 or other characters added like 12111)
=LET(in,A2,keys, C1:D1,
arrayCounts, LAMBDA(txt,key,LET(otherTxt,SUBSTITUTE(txt,key,""),nonkeys,IFERROR(UNIQUE(MID(otherTxt,SEQUENCE(LEN(otherTxt)),1)),CHAR(CODE(key)+1)), out, IFERROR(LEN(TEXTSPLIT(in,nonkeys,,1)),""),TRANSPOSE(out))),
out,DROP(REDUCE("",keys,LAMBDA(p,q, HSTACK(p,arrayCounts(in,q)))),,1),
IF(ISERROR(1/out),"",out))but that said to give Schuss27 a very very simple way it could be:
=LEN(TEXTSPLIT(A1,,"1",1))The above will create a column for ZEROs so replace the "1" with "0" to get ONEs
That is really short, I was into that direction, but you really simplified. I actually adapted my second formula from the first version (when the input is an array), so I could not see this shortcut for the string input.
- I credit you with the direction. I was focus on creating a generalizable solution which I still think is cool. They can handle any array of 'headers' as find keys and has most error checking in place, at least what I thought of as 'edge cases'. But that is what is great here, we can help/push each other and give lots of ways to do the same task.