Searching cell for text value from list

[g.lucas11]

Hi, I have been trying for DAYS to get this problem solved. I have a
list of stock symbols and a cell which may contain a value from the
list.

For example


Stock Symbols

ADNC
SEDG
EDSW
TFDE

And a cell that could contain a value from the list.
Eg.

"I think SEDG is a very popular stock."

I have seen on the net "code", but I'm sure there must be a formula
for this! I would be interested to see what it is.

PLEASE HELP!

 

[RichardSchollar]

Hi

Possibly (given your list in A1:A4 and the cell you are searching in
B1

=SUMPRODUCT(--ISNUMBER(FIND($A$1:$A$4,B1)))>0

returns True/False and is case sensitive (replace Find with Search if
you don't want it case sensitive).

Hope this helps!

Richard

 

[Don Guillett]

I'm not quite sure what you want??

If you mean that you want to find the cell in which the text resides look in
the vba help index for INSTR and incorporate that into a looping macro.

If you want a formula to find the string in a cell look in the regular help
index for FIND or SEARCH

 

[Ragdyer]

If I understand what you're looking for,
with your list in A1 to A4,
you could directly reference the cell containing the symbol,
and have it inserted within the sentence:

="I think "&A1&" is a very popular stock."
Changing the "A1" reference to any of the other symbol containing cells:
="I think "&A2&" is a very popular stock."
This of course, means revising the formula in each case.

To keep the formula static while still changing the symbol, you could try
something like this:

="I think "&INDIRECT(B1)&" is a very popular stock."
Where you just insert the cell reference of the desired symbol in B1 (A1,
A2, ... etc.)

 

[T. Valko]

See your other post for another possibility.

Biff

 

[g.lucas11]

Hi

Possibly (given your list in A1:A4 and the cell you are searching in
B1

=SUMPRODUCT(--ISNUMBER(FIND($A$1:$A$4,B1)))>0

returns True/False and is case sensitive (replace Find with Search if
you don't want it case sensitive).

Hope this helps!

Richard








- Show quoted text -

I really appreciate you taking the time out to give me a formula. This
one is a godsend. Thank you very much. I have had a look at it and
it's what I need as I have a long list of descriptions, so an array
formula would work but take a long time to insert into every cell. I
did notice that if there are blank in the list of stocks eg. SEDG and
so on, then some of the not true answers come up true, but this is
what I was after and to tell you the truth, beyond my Excel skills.

Thanks again.