check if value occurs within a range

[peacelittleone]

I have the following in column A :

A
1
2
3 4
3 4 5
3
4
5
1 3


How do I find out if the number 1 is found in Column A. Column
contains either a single entry or multiple entries separated b
"ALT+Enter" (Chr(10)).

I don't care if it is there more than once, only if it is there a
all.

Any suggestions?

TIA

Heather

 

[Bob Phillips]

Heather,

This any good?

=COUNTIF(A:A,1)+COUNTIF(A:A,"1"&CHAR(10)&"*")+COUNTIF(A:A,"*"&CHAR(10)&"1")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"peacelittleone"

 

[Aladin Akyurek]

Given that E2: 1

=(COUNTIF(A:A,E2)+COUNTIF(A:A,"*"&E2&"*"))>0

 

[peacelittleone]

This is what ended up working:

=COUNTIF($A$1:$A$7,B1)+COUNTIF($A$1:$A$7,B1&CHAR(10)&"*")+COUNTIF($A$1:$A$7,"*"&CHAR(10)&B1)+COUNTIF($A$1:$A$7,"*"&CHAR(10)&B1&CHAR(10)&"*")

Where I had to conver the values in column B to text:
=text(B1,"0")


Thanks for getting me started!

Heather.

 

[Aladin Akyurek]

I think you want the formula distinguish between true 1 and 1 that
occurs in such items like 11...

=SUMPRODUCT(ISNUMBER(SEARCH(CHAR(10)&B1&CHAR(10),CHAR(10)&$A$1:$A$7&CHAR(10)))+0)>0

would be more compact.