Searching a text string in an array

[Govind]

Hi ,

I would like to search a text string in an array and return the cell
ref. which matches the string.

For eg. i want to find out all occurences of "apples" in the following
array:
Cell Ref
A1 oranges
A2 Apples
A3 Apples and lemons
A4 Oranges and Bananas


The result should be cell A2 & A3.

Is there a formula to do this?

Regards

Govind.

 

[Don Guillett]

Have a look in HELP index for FIND. There is a good example there. Be sure
to use xlpart instead of xlwhole

 

[Govind]

Didnt get what you are referring to. Couldnt find any good solution in
the help index other than auto filter. What i need is not auto filter. I
need a formula

Govind.

 

[Frank Kabel]

Hi
to count try
=COUNTIF(A1:A100,"*apples*")

 

[Jason Morin]

It would help if we knew exactly what you are trying to
accomplish. You can use something like:

=ISNUMBER(SEARCH("apple",A1:A4))

to return the position of each within a 1 column array.
You'll get:

{FALSE,TRUE,TRUE,FALSE}

Where do you want to go from here? Do you really want to
return 2 cell references to the cell as text strings, or
is this part of a larger formula?

HTH
Jason
Atlanta, GA

 

[Govind]

Hi Frank,

That works for count. But what if i want to find out the cell
reference(or atleast the number of cells in the bottom from a particular
cell - Lets say the cell i need is A10, and the range is A1:A30, the
formula shoudl either return A10 or 9 in value (the position from cell A1)

Govind.

 

[Frank Kabel]

Hi
do you need only the first occurence? If yes try
=MIN(IF(ISNUMBER(FIND("apples",A1:A30)),ROW(A1:A30)))

array entered with CTRL+SHIFT+ENTER

 

[Don Guillett]

I meant VBA help. If you want to find all then a macro would be best.

 

[Govind]

This will help.

Thanks.

Govind.

Hi
do you need only the first occurence? If yes try
=MIN(IF(ISNUMBER(FIND("apples",A1:A30)),ROW(A1:A30)))

array entered with CTRL+SHIFT+ENTER