Max, first, thanks very much for your help. Next,the formula returns no text
value when I use it. Let me explain "verbally" what I'm trying to do and see
if this helps.

I have a worksheet with about 2000 rows.
Cells in Column A have company names.
Cells in Column B have the home country exchange and stock ticker.
Cells in Column G have all the stock exchanges where the stock is traded,
separated by comma.
I only want to look at the U.S. exchange:ticker information.

In the case of Nokia: HLSE is the home country stock exchange and NOK1V is
the home country stock ticker. I want HLSE:NOK1V replaced by any text value
in cell G1 that is preceeded by "NYSE:" , "AMEX:" , "NasdaqNM:", "NasdaqSC:"
..

So for example, I would like a formula to search for any piece of text that
is preceeded by the 4 examples above (NYSE:, etc.) and return that whole
piece of text in another cell (Column H is fine).

In the example I gave above, the formula would find the "NYSE:" in
"NYSE:NOK" in cell G1, and based on that, return "NYSE:NOK" into cell H1.

I'd like to be able to drag this formula down 2000 rows so it would work
with any stock ticker preceed by "NYSE:" etc...

So if AMEX:ABC is 1 of many text strings in cell G2, separated by commas,
it would recogize the "AMEX:" and return the and return the entire text
string "AMEX:ABC" in cell H2.

The stock tickers (the text following the colon) could be any number of
characters, most probably between 1-4 characters, sometimes 5 or more (A, AB,
ABC, C, CA, F, FBAC, etc....)

So to summarize:
1) search for text preceeded by the 4 text strings above ("NYSE:" in the
text string "NYSE:NOK" or "NYSE:" in "NYSE:ABC", etc)
2) return the entire piece of text in a cell in column H.

Thanks very much for taking a look and thinking about this!


So in the case I described above, cell G1 has all of the stock exchanges
where Nokia trades. If any of the stock tick