Compare String in Cell to an Array

[Guest]

I am wanting to take a string in a cell containing multiple data elements to
an array and have the data in the cell either trimmed or to highlight the
element that is matched in the array.

Example:

Cell B2 contains:
MROMANO | MTELLIG | JGURECK | MWOLK | LGAZDA | GVANBUH | CSHOAF | STILLER |
RVERBIC | DSTEPHE | BLANGLI

Cells AA2:AA10 contain the array for comparison.

I am not sure where to proceed from here since this appears a bit complex
from the onset. Thanks in advance for any insights.

 

[Guest]

Hi,
What kind of comparison, what should the result be?
1- Return true if at least one element of B2 is found in AA2:AA10,
2- or Return an array of all elements of B2 found in AA2:AA10
3- or return a pipe (|) delimited string with all elements of B2 found in
AA2:AA10
4- or return the cells of AA2:AA10 having an matching element in B2
....

 

[Guest]

Use the Find (case sensitive) or Search functions (case insensitive). These
are worksheet functions. See Excel help for details.

 

[Guest]

I believe the best result would be for it to return the value(s) that match
one of the elementos in AA2:AA10. The majority of the time, it will just be
one, but it could be two values.

From the example below, if I could have B2 contain the string and a formula
in C2, C2 would give me the result of the match. For instance, AA2:AA10
contains:

ACLEMON
BDUFOUR
BLANGLI
DDILUCE
DWELLS
ESCOTT
KRENKER
THUMENI
WROCHES

If B2 contains:

MROMANO | MTELLIG | JGURECK | MWOLK | LGAZDA | GVANBUH | CSHOAF | STILLER |
RVERBIC | DSTEPHE | BLANGLI

C2 should return a result of BLANGLI. If there is more than one piece of
matching data, then it would be great if C2 displayed the multiple items in a
new pipe-delimited string. Then, I want to do this for the remaining cells
(B3 to B146) in a successive list. Hopefully, I've been a little more
succinct in my request this time. =)

Thanks again!

 

[Guest]

Those functions don't quite do this the way I need it to. Here is a little
better explanation:

I believe the best result would be for it to return the value(s) that match
one of the elementos in AA2:AA10. The majority of the time, it will just be
one, but it could be two values.

From the example below, if I could have B2 contain the string and a formula
in C2, C2 would give me the result of the match. For instance, AA2:AA10
contains:

ACLEMON
BDUFOUR
BLANGLI
DDILUCE
DWELLS
ESCOTT
KRENKER
THUMENI
WROCHES

If B2 contains:

MROMANO | MTELLIG | JGURECK | MWOLK | LGAZDA | GVANBUH | CSHOAF | STILLER |
RVERBIC | DSTEPHE | BLANGLI

C2 should return a result of BLANGLI. If there is more than one piece of
matching data, then it would be great if C2 displayed the multiple items in a
new pipe-delimited string. Then, I want to do this for the remaining cells
(B3 to B146) in a successive list.

Regards,
Jeff Bloomer
http://www.standardregister.com