Finding if a number appears in a certain cell

[sunnydee003]

Thank you for looking at my post. I want to know how to do the following.

If I want to know how many times the number 2 appears in a set of numbers that are in one cell.

The set of numbers in cell A2:
12 02 44 26 91 55 62

I've tried the formula:=ISNUMBER(SEARCH(SUBSTRING,TEXT)) to find it but it'll give me a TRUE for every time there is a 2 in the set of numbers, even if its the number 12.

 

[Rick Rothstein]

I've tried the formula:

=ISNUMBER(SEARCH(" "&SUBSTRING&" "," "&TEXT&" "))

to find it but it'll give me a TRUE for every time there is a 2 in the set of numbers, even if its the number 12.

Try concatenating spaces (since that is the character between your numbers) on to the ends of both the text being searched for and the text being searched as shown above in red.

 

[sunnydee003]

Try concatenating spaces (since that is the character between your numbers) on to the ends of both the text being searched for and the text being searched as shown above in red.

That works but then I run into the problem of getting a "FALSE" for the number 2 I'm looking for because in the cell, its written as 02 and my reference is just 2. How do I make it so that numbers from 1-9 have a 0 in front of them (example: 01, 02, 03...)?

 

[Rick Rothstein]

That works but then I run into the problem of getting a "FALSE" for the number 2 I'm looking for because in the cell, its written as 02 and my reference is just 2. How do I make it so that numbers from 1-9 have a 0 in front of them (example: 01, 02, 03...)?

Are the numbers in the cells (the ones separated by spaces) always two digits long?

 

[jtakw]

Hi,

In your OP, you said "how many times the number....", so I assume there may be 0, 1, 2 or more times the search value may appear within the Text string.
Assuming the substrings are Always 2 digits, you can do one of these:

Book1
	A	B	C	D	E	F	G	H	I
1	02 12 02 44 26 91 02 55 62 02		4	4	02	4	2	0	99
2	02 12 44 26 91 55 62		1	1		1		0
3	12 02 44 26 91 55 62		1	1		1		0
4	12 44 26 91 55 62 99		0	0		0		1
5	12 44 26 91 55 62 02		1	1		1		0
6	12 02 44 26 91 02 55 62		2	2		2		0
7	02 12 44 26 91 55 62 02		2	2		2		0
Sheet154

Cell Formulas
Range	Formula
C1	=(LEN(A1)-LEN(TRIM(SUBSTITUTE(A1,"02",""))))/3
D1	=(LEN(A1)-LEN(TRIM(SUBSTITUTE(A1,E$1,""))))/3
F1	=(LEN(A1)-LEN(TRIM(SUBSTITUTE(A1,IF(LEN(G$1)=1,0,"")&G$1,""))))/3

C1 formula has the search value "hard-coded" in the formula.
D1 formula uses a cell reference (i.e. E1), so you can change it at will (please note E1 must have 2 digits - entered as text due to leading 0)
F1 and H1 (Columns H and I are for illustration purposes only) formula is the same, uses a cell reference (i.e. G1) where you can enter either a single or double digit number for the search.