software

here is what Iquot;m looking for. I don't know if it needs to be a macro.
Thanks for taking a look:

If text in column B does not contain any text that contains NYSE:,
NasdaqNM:, NasdaqSC:, Amex:, then find text in column G that does contain
NYSE:, NasdaqNM:, NasdaqSC:, Amex: and return that entire phrase of text to
column b.

here is the data set:
Column A Column B ... Column G
Nokia HLSE:NOK1V HLSE:NOK1V, BMV:NOK.N, ENXTAM:NOKA, NYSE:NOK

The cell in Column G has a lot more text than showed here.

Then I would like the data in Column B replaced with NYSE:NOK.

When I say Column, I am referring to a particular cell in the Column. This
worksheet has about 2000 rows of cells in each column.

Thanks for your help!
Here's one crack at this ..

Put in say, H1:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{quot;NYSE:,quot;;quot;NasdaqNM:,quot;;quot;NasdaqS C:,quot;;quot;Ame
x:,quot;})))gt;0,quot;NYSE:NOKquot;,IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{quot;NYSE:,quot;;quot;NasdaqNM
:,quot;;quot;NasdaqSC:,quot;;quot;Amex:,quot;})))gt;0,G1,quot;quot;))

Copy H1 down to H2000 ?

Then copy col H and overwrite col B with a paste special gt; values, then
delete col H

Above presumes that quot;nothingquot;, i.e.: quot;quot;
is to be returned if both conditions are not satisfied
(no hint was deduced from your post)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;SteveCquot; gt; wrote in message
...
gt; here is what Iquot;m looking for. I don't know if it needs to be a macro.
gt; Thanks for taking a look:
gt;
gt; If text in column B does not contain any text that contains NYSE:,
gt; NasdaqNM:, NasdaqSC:, Amex:, then find text in column G that does contain
gt; NYSE:, NasdaqNM:, NasdaqSC:, Amex: and return that entire phrase of text
to
gt; column b.
gt;
gt; here is the data set:
gt; Column A Column B ... Column G
gt; Nokia HLSE:NOK1V HLSE:NOK1V, BMV:NOK.N, ENXTAM:NOKA,
NYSE:NOK
gt;
gt; The cell in Column G has a lot more text than showed here.
gt;
gt; Then I would like the data in Column B replaced with NYSE:NOK.
gt;
gt; When I say Column, I am referring to a particular cell in the Column.
This
gt; worksheet has about 2000 rows of cells in each column.
gt;
gt; Thanks for your help!
gt;
gt;
gt; Put in say, H1:
gt;
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{quot;NYSE:,quot;;quot;NasdaqNM:,quot;;quot;NasdaqS C:,quot;;quot;Ame
gt;
x:,quot;})))gt;0,quot;NYSE:NOKquot;,IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{quot;NYSE:,quot;;quot;NasdaqNM
gt; :,quot;;quot;NasdaqSC:,quot;;quot;Amex:,quot;})))gt;0,G1,quot;quot;))

Correction, apologies, got it the wrong way round
the formula in H1 should read:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH({quot;NYSE:,quot;;quot;NasdaqNM:,quot;;quot;NasdaqSC:, quot;;quot;Amex:,
quot;},B1)))gt;0,quot;NYSE:NOKquot;,IF(SUMPRODUCT(--ISNUMBER(SEARCH({quot;NYSE:,quot;;quot;NasdaqNM:,quot;
;quot;NasdaqSC:,quot;;quot;Amex:,quot;},B1)))gt;0,G1,quot;quot;))

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Max, first, thanks very much for your help. Next,the formula returns no text
value when I use it. Let me explain quot;verballyquot; 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 quot;NYSE:quot; , quot;AMEX:quot; , quot;NasdaqNM:quot;, quot;NasdaqSC:quot;
..

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 quot;NYSE:quot; in
quot;NYSE:NOKquot; in cell G1, and based on that, return quot;NYSE:NOKquot; 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 quot;NYSE:quot; etc...

So if AMEX:ABC is 1 of many text strings in cell G2, separated by commas,
it would recogize the quot;AMEX:quot; and return the and return the entire text
string quot;AMEX:ABCquot; 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 (quot;NYSE:quot; in the
text string quot;NYSE:NOKquot; or quot;NYSE:quot; in quot;NYSE:ABCquot;, 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

gt; In the example I gave above,
gt; the formula would find the quot;NYSE:quot; in quot;NYSE:NOKquot; in cell G1,
gt; and based on that, return quot;NYSE:NOKquot; into cell H1.

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

Paste this into the formula bar for H1:

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

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 quot;NYSE:quot; amp; quot;AMEX:quot;, 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: quot;AMEX:ABC,quot; in the example for G1 above
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;SteveCquot; gt; wrote in message
...
gt; Max, first, thanks very much for your help. Next,the formula returns no
text
gt; value when I use it. Let me explain quot;verballyquot; what I'm trying to do and
see
gt; if this helps.
gt;
gt; I have a worksheet with about 2000 rows.
gt; Cells in Column A have company names.
gt; Cells in Column B have the home country exchange and stock ticker.
gt; Cells in Column G have all the stock exchanges where the stock is traded,
gt; separated by comma.
gt; I only want to look at the U.S. exchange:ticker information.
gt;
gt; In the case of Nokia: HLSE is the home country stock exchange and NOK1V
is
gt; the home country stock ticker. I want HLSE:NOK1V replaced by any text
value
gt; in cell G1 that is preceeded by quot;NYSE:quot; , quot;AMEX:quot; , quot;NasdaqNM:quot;,
quot;NasdaqSC:quot;
gt; .
gt;
gt; So for example, I would like a formula to search for any piece of text
that
gt; is preceeded by the 4 examples above (NYSE:, etc.) and return that whole
gt; piece of text in another cell (Column H is fine).
gt;
gt; In the example I gave above, the formula would find the quot;NYSE:quot; in
gt; quot;NYSE:NOKquot; in cell G1, and based on that, return quot;NYSE:NOKquot; into cell H1.
gt;
gt; I'd like to be able to drag this formula down 2000 rows so it would work
gt; with any stock ticker preceed by quot;NYSE:quot; etc...
gt;
gt; So if AMEX:ABC is 1 of many text strings in cell G2, separated by commas,
gt; it would recogize the quot;AMEX:quot; and return the and return the entire text
gt; string quot;AMEX:ABCquot; in cell H2.
gt;
gt; The stock tickers (the text following the colon) could be any number of
gt; characters, most probably between 1-4 characters, sometimes 5 or more (A,
AB,
gt; ABC, C, CA, F, FBAC, etc....)
gt;
gt; So to summarize:
gt; 1) search for text preceeded by the 4 text strings above (quot;NYSE:quot; in the
gt; text string quot;NYSE:NOKquot; or quot;NYSE:quot; in quot;NYSE:ABCquot;, etc)
gt; 2) return the entire piece of text in a cell in column H.
gt;
gt; Thanks very much for taking a look and thinking about this!
gt;
gt;
gt; So in the case I described above, cell G1 has all of the stock exchanges
gt; where Nokia trades. If any of the stock tick
A sample implementation is available at:
www.savefile.com/files/5209535
Extract complete substring if keystring found.xls
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Here's a variation of the solution offered by Max...

1) List the key text strings in a range of cells, let's say Z2:Z5.

2) Define (Insert gt; Define gt; Name) the following...

Name: BigNum

Refers to:

=9.99999999999999E 307

Click Add

Name: List

Refers to:

=$Z$2:$Z$5

Click Ok

3) Then try the following formula...

=MID(C2,LOOKUP(BigNum,FIND(List,C2)),FIND(quot;,quot;,C2amp;quot; ,quot;,LOOKUP(BigNum,FIND(L
ist,C2)))-LOOKUP(BigNum,FIND(List,C2)))

Note that the formula is case-sensitive. If you want the formula to be
case-insensitive, replace FIND with SEARCH.

Hope this helps!

In article gt;,
SteveC gt; wrote:

gt; here is what Iquot;m looking for. I don't know if it needs to be a macro.
gt; Thanks for taking a look:
gt;
gt; If text in column B does not contain any text that contains NYSE:,
gt; NasdaqNM:, NasdaqSC:, Amex:, then find text in column G that does contain
gt; NYSE:, NasdaqNM:, NasdaqSC:, Amex: and return that entire phrase of text to
gt; column b.
gt;
gt; here is the data set:
gt; Column A Column B ... Column G
gt; Nokia HLSE:NOK1V HLSE:NOK1V, BMV:NOK.N, ENXTAM:NOKA, NYSE:NOK
gt;
gt; The cell in Column G has a lot more text than showed here.
gt;
gt; Then I would like the data in Column B replaced with NYSE:NOK.
gt;
gt; When I say Column, I am referring to a particular cell in the Column. This
gt; worksheet has about 2000 rows of cells in each column.
gt;
gt; Thanks for your help!

Max, thanks so much for the solution and for posting the solution Excel.

One small thing: When I use it, I get a #VALUE error -- this is because the
last set of text strings is not followed by a comma. For example you
reference in G1:

HLSE:NOK1V, HLSE:NOK1V, BMV:NOK.N, ENXTAM:NOKA, NYSE:NOK,

But it should reference instead:
HLSE:NOK1V, HLSE:NOK1V, BMV:NOK.N, ENXTAM:NOKA, NYSE:NOK

You see, no comma at the end of NYSE:NOK -- it's because of this no comma
that I get a #VALUE error. Any suggestions?

As for the solution only returning 1 text string, not 2 or more if there are
more than 1, that's fine, I only need one... thanks...

Thanks again! This is really terrific stuff.

Domenic, a true work of art! Thank you.

Not to mar your elegant work with a beginner's learning, but: Assuming the
primary exchange is in Cell B1, all exchanges are in Cell G1, I modified the
formula to return the Primary Exchange if it Doesn't find a U.S. Exchange in
Cell G1:

=IF(ISERROR(MID(G1,LOOKUP(BigNum,FIND(List,G1)),FI ND(quot;,quot;,G1amp;quot;,quot;,LOOKUP(BigNum,FIND(List,G1)))-LOOKUP(BigNum,FIND(List,G1)))),B1,(MID(G1,LOOKUP(B igNum,FIND(List,G1)),FIND(quot;,quot;,G1amp;quot;,quot;,LOOKUP(BigNum ,FIND(List,G1)))-LOOKUP(BigNum,FIND(List,G1)))))

Please tell me, what is the logic of assigning BigNum with all of this? I
don't understand the logic of the formula.

Thanks, very slick.

Out of simple curiosity, I wonder if there is a way to prioritize the search.
So for example, by priority:

1) NYSE
2) AMEX
3) NasdaqNM
4) NasdaqSM

If there is more than U.S Exchange listed, it will return by priority. For
example, if there is a NYSE and NasdaqNM string present, it will return the
NYSE string (priority 1 versus priority 3).

If there is a NasdaqNM and NasdaqSM string, it will return the NasdaqNM
string (priority 3 versus priority 4).

Thanks again...