Tip, a combination of functions COUNTIF + REPT

[bines53]

Hello friends ,

Follow the challenge here

https://chandoo.org/forum/threads/add-space-between-digits.37136/

One of the problems with the function COUNTIF ,
He can not work with multiple zeros in cells,

Suppose that in cell A1, given 20072505A,Text format.

We want to know if 007 is somewhere in the cell ,In cell C1, we PUT the number 7,

This formula will look for the number 7 ,=COUNTIF(A1,"*"&C1&"*"),

But this formula will be searched 07 =COUNTIF(A1,"*"&TEXT(C1,REPT(0,2))&"*")

And =COUNTIF(A1,"*"&TEXT(C1,REPT(0,3))&"*") will be searched 007 ,

And =COUNTIF(A1,"*"&TEXT(C1,REPT(0,1))&"*") will be searched 7 ,

We see that function REPT controls, and causes COUNTIF function, to work properly.

Of course you can place 0 instead of 7, and then search ,0,00,000 and so.

I hope you understand my English.


David

 

[John Jairo V]

Hi, bines53!

What is the problem with:
=COUNTIF(A1,"*00"&C1&"*")

apparently it works without problem. Blessings!

 

[bines53]

Hi John jairo V ,

My friend ,

A2078 ,Put it with your formula and you'll see .


David

 

[Chihiro]

? I don't get the difference...

007 equivalent formula
=COUNTIF(A1,"*"&TEXT(C1,REPT(0,3))&"*")
Won't work either on that value.

You just have to use...
=COUNTIF(A1,"*"&TEXT(C1,REPT(0,2))&"*")
Or
=COUNTIF(A1,"*0"&C1&"*")

No?

Or just rept without text function.
=COUNTIF(A1,"*"&REPT(0,1)&C1&"*")

 

[John Jairo V]

A2078 ,Put it with your formula and you'll see .
David

This formula not works with your input:
=COUNTIF(A1,"*00"&C1&"*")
Because there aren't two zeros before the seven in the cell.

But this works well:
=COUNTIF(A1,"*0"&C1&"*")

Likewise, in this input: A20078
it Works: =COUNTIF(A1,"*00"&C1&"*")

I don't see any difference using REPT or not. Blessings!

 

[bines53]

Hi Chihiro,

I see that my English is really bad !


First off you must also the TEXT function, to get accurate results.

To search for a 007 pattern, that is, from left to right =COUNTIF(A1,"*"&TEXT(C1,REPT(0,3))&"*")


To search for a 07 pattern, that is, from left to right =COUNTIF(A1,"*"&TEXT(C1,REPT(0,2))&"*")


To search for a 7 =COUNTIF(A1,"*"&TEXT(C1,REPT(0,1))&"*") OR

=COUNTIF(A1,"*"&C1&"*"),


David

 

[bines53]

Hi John jairo V,

Take a 10-line range, and tell me which formula you gave, giving a correct count of 7, 07, 007. When each cell shows 0, you can think the formula is correct, the question of whether it is accurate.


David

 

[bines53]

Hi John jairo V,

I did a test, there really is not any difference, I did not know your formula before.

David

 

[Lori]

Hi David,

I agree that COUNTIF can sometimes give seemingly erratic results. Here's a related example:

Format cells A1:A3 as text, and enter in these cells (in any order):
007
07
7

Now try the following three formulas:
=COUNTIF(A1:A3,A1)
=COUNTIF(A1:A3,"="&A1)
=COUNTIF(A1:A3,"<="&A1)

These return 3, 3 and 0 respectively??

A tip I learned a few weeks ago and don't think is widely known is to insert an additional "soft hyphen" character when comparing text that contains numbers, ie:

=COUNTIF(A1:A3,CHAR(173)&A1)

This returns the expected result of 1.

Rgds
Lori

 

[bines53]

Hi Lori,


Until a month ago I had not experienced text functions, because I did not need it.
I understood that function REPT is very interesting, and unique.

=COUNTIF(A1,"*00"&C1&"*"),=COUNTIF(A1,"*"&TEXT(C1,REPT(0,3))&"*"),

After all, there is no difference between them,Return the same result.

But with REPT function, the formula is flexible and efficient.

And in your example,=COUNTIF(A1:A3,"*"&TEXT(A1,REPT(0,3))&"*"),


David