Searching cell for 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!

 

[Dave Peterson]

You have an active thread elsewhere.

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!

 

[T. Valko]

I'm sure there must be a formula for this!

For what? You haven't clearly stated what it is you want to do!

So far, all we know is that you have a list of stock symbols and might have
a cell with a sentence that might contain one of those symbols. What do you
want to do? Do you want to know if a symbol from the list is contained in
that sentence?

Here's something that might get you started.

Assume the list of symbols is in the range A2:A5

C2 = I think SEDG is a very popular stock.

This formula will return SEDG because it is in both the list and the
sentence. Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=INDEX(A2:A5,MATCH(TRUE,ISNUMBER(SEARCH(A2:A5,C2)),0))

If no symbol from the list is found in the sentence the formula will return
#N/A.

Biff

 

[Don Guillett]

Pls post in ONE group only

 

[g.lucas11]

Pls post in ONE group only

--
Don Guillett
SalesAid Software










- Show quoted text -

Sorry I was becoming inpatient, as this problem has been bugging me
for days. I'm sure you'll understand my frustration.

 

[g.lucas11]

For what? You haven't clearly stated what it is you want to do!

So far, all we know is that you have a list of stock symbols and might have
a cell with a sentence that might contain one of those symbols. What do you
want to do? Do you want to know if a symbol from the list is contained in
that sentence?

Here's something that might get you started.

Assume the list of symbols is in the range A2:A5

C2 = I think SEDG is a very popular stock.

This formula will return SEDG because it is in both the list and the
sentence. Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=INDEX(A2:A5,MATCH(TRUE,ISNUMBER(SEARCH(A2:A5,C2)),0))

If no symbol from the list is found in the sentence the formula will return
#N/A.

Biff











- Show quoted text -

THANK YOU SOOOOOOO much!!!!

I have been going over and over in my head trying to find a solution.
Also, thanks for the speedy reply.

Sorry, I'll explain what I really want from this. I just needed some
sort of help!

I have a long list of details about stocks, in column A. Within this
"sentence" or "paragraph" could be a symbol from a long range of stock
symbols. (I hope I'm making sense!?) Your formula is FANTASTIC for one
cell containing a paragraph. But I have many "paragraphs".

I have two options. Do CTRL SHIFT ENTER on all the cells or ..... you
give me another formula ha ha!

Thanks again, I just needed a way to find whether the cell contains
that value. To return true or false and so on and your formula is a
good start. If there is no other way then I'll do it for many cells.

 

[Tom Ogilvy]

I'm sure you'll understand my frustration

No, I don't think we do.

http://tinyurl.com/2sxo8a

Looks like you are establishing a pattern of multiposting (back on the 6th
of Jan, 3 groups in 3 minutes).

Maybe you understand our frustration at wasting everyone's time.

 

[T. Valko]

Ok, now we know what you want!

For a result of either TRUE or FALSE, use this (still an array formula:
CTRL,SHIFT,ENTER):

=OR(ISNUMBER(SEARCH(A2:A5,C2)))

If you have a range of cells like this, say, C2:C10 and you want this
formula result in D210, then enter the formula in D2 as:

=OR(ISNUMBER(SEARCH(A$2:A$5,C2)))

and drag copy down to D10.

Biff

 

[g.lucas11]

No, I don't think we do.

http://tinyurl.com/2sxo8a

Looks like you are establishing a pattern of multiposting (back on the 6th
of Jan, 3 groups in 3 minutes).

Maybe you understand our frustration at wasting everyone's time.

--
Regards,
Tom Ogilvy







- Show quoted text -

Once again I didn't know the "rules". Sorry to waste your time.

 

[David Biddulph]

Just a few starters for you, if you still don't understand the difference
between crossposting and multiposting.

http://www.cs.tut.fi/~jkorpela/usenet/xpost.html
http://oakroadsystems.com/genl/unice.htm#xpost
http://www.webhero.org/Newsgroups/crossposting.htm
http://smjg.port5.com/faqs/usenet/xpost.html