Return the number of occurrences of a number

Question

enter image description here

Right now my issue, is that if I want to search for '263', it returns 0 as I believe excel thinks the entire cell is one string.

I have tried using FIND and SEARCH but it doesn't return the correct number.

you could take a look at this article for more advanced searches, examples there include extracting numbers from strings: https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml/61837697#61837697 — tnavidi, Sep 09 '22 at 10:22
Please show the exact formulas you have tried. As @SolarMike writes, you can use `COUNTIF` with wild cards. However, if there might be more than a single `263` in a cell, and you need to count both, you could use `FILTERXML` depending on your version of Excel. — Ron Rosenfeld, Sep 09 '22 at 10:44
@SolarMike: as you can see, my answer contains the formula `=COUNTIF(A1:A6,"*263*")`. Unfortunately, when the cell contains only the number 263, then that cell won't be counted. Do you know why and do you have a simple solution for that? — Dominique, Sep 09 '22 at 12:09
@Dominique so have 2 countif() like so: COUNTIF(A1:A6,263)+COUNTIF(A1:A6,"*263*") but a helper column or text-to-columns may be an easier option... — Solar Mike, Sep 09 '22 at 12:14

score 1 · Answer 1 · answered Sep 09 '22 at 11:29

1

I just managed as follows:

=COUNTIF(A1:A6,"*263*")

Be aware that an entry like 263 lab | 263 will yield 2 count results.

answered Sep 09 '22 at 11:29

Dominique

1

Not so. `COUNTIF` will only return 1 count no matter how many `263`'s are in a cell. Try: `A1 = 263 lab | 263`. `B1: = OUNTIF(A1,"*263")`. Result => `1` – Ron Rosenfeld Sep 10 '22 at 02:19

score 0 · Answer 2 · answered Sep 09 '22 at 12:22

So, just to clarify my comment adding to @Dominique :

And, you will notice it only captures 1 for the entry in cell A5

You can try each of the following:

COUNTIF(A1:A6,"*263*")

COUNTIF(A1:A6,"263*")

COUNTIF(A1:A6,"*263")

COUNTIF(A1:A6,263)

Separately to see what happens.

2 Answers2