Searching a text string in an array

  • Thread starter Thread starter Govind
  • Start date Start date
G

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.
 
Have a look in HELP index for FIND. There is a good example there. Be sure
to use xlpart instead of xlwhole
 
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.
 
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
 
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.
 
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
 
This will help.

Thanks.

Govind.

Frank said:
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
Click to expand...
 
Back
Top