ExcelBanter - Searching cell for value from list

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Searching cell for value from list (https://www.excelbanter.com/excel-discussion-misc-queries/130230-searching-cell-value-list.html)

Searching cell for value from list

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!

Searching cell for value from list

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

wrote in message
ups.com...
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!

Searching cell for value from list

On 11 Feb, 19:00, "Don Guillett" wrote:
Pls post in ONE group only

--
Don Guillett
SalesAid Software
wrote in message

ups.com...

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!- Hide quoted text -

- 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.

Searching cell for value from list

On 11 Feb, 19:00, "T. Valko" wrote:
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

wrote in message

ups.com...

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!- Hide quoted text -

- 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.

Searching cell for value from list

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.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
On 11 Feb, 19:00, "Don Guillett" wrote:
Pls post in ONE group only

--
Don Guillett
SalesAid Software
wrote in message

ups.com...

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!- Hide quoted text -

- 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.

Searching cell for value from list

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 D2:D10, then enter the formula in D2 as:

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

and drag copy down to D10.

Biff

wrote in message
oups.com...
On 11 Feb, 19:00, "T. Valko" wrote:
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

wrote in message

ups.com...

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!- Hide quoted text -

- 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.

Searching cell for value from list

On 11 Feb, 21:34, "Tom Ogilvy" wrote:
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.

--
Regards,
Tom Ogilvy

wrote in message

ups.com...

On 11 Feb, 19:00, "Don Guillett" wrote:
Pls post in ONE group only

--
Don Guillett
SalesAid Software
wrote in message

roups.com...

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!- Hide quoted text -

- 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.- Hide quoted text -

- Show quoted text -

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

Searching cell for value from list

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
--
David Biddulph

wrote in message
ups.com...

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

On 11 Feb, 21:34, "Tom Ogilvy" wrote:
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.

--
Regards,
Tom Ogilvy

wrote in message

ups.com...

On 11 Feb, 19:00, "Don Guillett" wrote:
Pls post in ONE group only