• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Sequence in text

deciog

deciog

Active Member
Good Morning!

Once again I come to you for help.

I'm trying to count occurrences in a sequence of numbers / text in a cell.

I have in a cell a sequence like this:
02300670001112140017180200222324000270000283303400035041000

I would like to count how often 0 occurs according to a criterion, which would be 0, 00, 000 or 0000, and so on.

Thank you very much in advance

Decio
 

Attachments

  • Modelo.xlsx
    10.2 KB · Views: 13
Hi deciog,

There are 59 characters of numbers in a cell, is it always only numbers, and what is the maximum length of characters that can be?

David
 
Sorry I'm late, Thank you David and Marc L

Yes, you will always have a number and 59 characters

Count how many are equal to 0
Count how many are equal to 00
Count how many are equal to 000
Count how many are equal to 0000
Count how many are equal to 00000

I tried with this formula
= SUM (N (ISNUMBER (SEARCH (REPT ($ I4, ROW ($ 1: $ 60)), $ C $ 4))))
this is it
= IFERROR (MATCH (1.0 / SEARCH (REPT ($ N4, ROW ($ 1: $ 59), $ C $ 4)), 0)

Did not work

look at model 2
 

Attachments

  • Modelo 2.xlsx
    10.7 KB · Views: 6
Last edited:
The beginner way well works with a helper column
starting from the bigger / last criteria (aka "00000")
to substitute it by an empty string then you can compare the length
between original and after substitution (deletion)
and divide by the number of chars of the criteria …
​
 
Thank you very much I was far from the solution.

Thanks to both Bines53 and Bosco, the two formula works perfectly,

Fantastic
Decio
 
You must log in or register to reply here.
Back
Top