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.

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

Paste this into the formula bar for H1:

=MID(G1,IF(ISNUMBER(SEARCH("NYSE:",G1)),SEARCH("NY SE:",G1),IF(ISNUMBER(SEARC
H("NasdaqNM:",G1)),SEARCH("NasdaqNM:",G1),IF(ISNUM BER(SEARCH("NasdaqSC:",G1)
),SEARCH("NasdaqSC:",G1),IF(ISNUMBER(SEARCH("Amex: ",G1)),SEARCH("Amex:",G1),
"")))),SEARCH(",",G1,IF(ISNUMBER(SEARCH("NYSE:",G1 )),SEARCH("NYSE:",G1),IF(I
SNUMBER(SEARCH("NasdaqNM:",G1)),SEARCH("NasdaqNM:" ,G1),IF(ISNUMBER(SEARCH("N
asdaqSC:",G1)),SEARCH("NasdaqSC:",G1),IF(ISNUMBER( SEARCH("Amex:",G1)),SEARCH
("Amex:",G1),"")))))-IF(ISNUMBER(SEARCH("NYSE:",G1)),SEARCH("NYSE:",G1) ,IF(I
SNUMBER(SEARCH("NasdaqNM:",G1)),SEARCH("NasdaqNM:" ,G1),IF(ISNUMBER(SEARCH("N
asdaqSC:",G1)),SEARCH("NasdaqSC:",G1),IF(ISNUMBER( SEARCH("Amex:",G1)),SEARCH
("Amex:",G1),"")))))

Copy H1 down

The above seems to return the desired results

Note that it's assumed only one out of the 4 key strings:

NYSE:,
NasdaqNM:,
NasdaqSC:,
Amex:,

would be present within any one cell in col G

If there's more than one key string occurrence,
then only the corresponding results associated with
the first key string found
(in the checking sequence shown above)
would be returned in col H

For eg: if G1 houses both "NYSE:" & "AMEX:", viz:

ENXTAM:NOKA, NYSE:NOK, AMEX:ABC,

H1 will always return: NYSE:NOK
regardless of whether AMEX is to the left or right of NYSE
since NYSE is checked ahead of AMEX
(unless we change the check sequence within the formula)

And the comma separation is also presumed present
even if the item found is the last text string within the cell in col G
eg: the last string: "AMEX:ABC," in the example for G1 above
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"SteveC" wrote in message
...
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