Matching Text with With Certain Criteria

[Guest]

here is what I"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!

 

[Max]

Here's one crack at this ..

Put in say, H1:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{"NYSE:,";"NasdaqNM:,";"NasdaqSC:,";"Ame
x:,"})))>0,"NYSE:NOK",IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{"NYSE:,";"NasdaqNM
:,";"NasdaqSC:,";"Amex:,"})))>0,G1,""))

Copy H1 down to H2000 ?

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

Above presumes that "nothing", i.e.: ""
is to be returned if both conditions are not satisfied
(no hint was deduced from your post)

 

[Max]

Put in say, H1:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{"NYSE:,";"NasdaqNM:,";"NasdaqSC:,";"Ame
x:,"})))>0,"NYSE:NOK",IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{"NYSE:,";"NasdaqNM
:,";"NasdaqSC:,";"Amex:,"})))>0,G1,""))

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


---

 

[Guest]

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

 

[Max]

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("NYSE:",G1),IF(ISNUMBER(SEARC
H("NasdaqNM:",G1)),SEARCH("NasdaqNM:",G1),IF(ISNUMBER(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]

A sample implementation is available at:
http://www.savefile.com/files/5209535
Extract complete substring if keystring found.xls

---

 

[Domenic]

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 > Define > 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(",",C2&",",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!

 

[Guest]

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.

 

[Guest]

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)),FIND(",",G1&",",LOOKUP(BigNum,FIND(List,G1)))-LOOKUP(BigNum,FIND(List,G1)))),B1,(MID(G1,LOOKUP(BigNum,FIND(List,G1)),FIND(",",G1&",",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.

 

[Guest]

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

 

[Domenic]

Domenic, a true work of art! Thank you.

You're very welcome! Although part of the credit should go to Max. I
started to go in a slightly different direction until I saw his
solution.

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)),FIND(",",G1&",",LOOKUP(BigNum,
FIND(List,G1)))-LOOKUP(BigNum,FIND(List,G1)))),B1,(MID(G1,LOOKUP(BigNum,FIND(L
ist,G1)),FIND(",",G1&",",LOOKUP(BigNum,FIND(List,G1)))-LOOKUP(BigNum,FIND(List
,G1)))))

=IF(OR(ISNUMBER(FIND(List,C2))),MID(C2,LOOKUP(BigNum,FIND(List,C2)),FIND(
",",C2&",",LOOKUP(BigNum,FIND(List,C2)))-LOOKUP(BigNum,FIND(List,C2))),B2
)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

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

BigNum, which we've defined as 9.99999999999999E+307, is the largest
number recognized by Excel. When used as a lookup value for the LOOKUP
function, the last value in the lookup array is returned. For
additional information, see the following link...

http://www.mrexcel.com/board2/viewtopic.php?t=105725

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

Simply reverse the order of the list entered in Z2:Z5...

NasdaqSM:
NasdaqNM:
AMEX:
NYSE:

Hope this helps!

 

[Domenic]

See other thread...

 

[Max]

because the last set of text strings

is not followed by a comma.

Steve, one way to tame the beast in col H <g>, is
by attaching a comma to the last entry in col G instead

A simple: =TRIM(G1)&","
placed in say, I1, then I1 copied down,
col I copied and pasted to overwrite col G
(paste special > values)
should do the job in under a minute

Then col H will work ..

---

 

[Domenic]

Forgot to change the references to match your worksheet. Just in case
there's any confusion, the formula should be as follows...

=IF(OR(ISNUMBER(FIND(List,G1))),MID(G1,LOOKUP(BigNum,FIND(List,G1)),FIND(
",",G1&",",LOOKUP(BigNum,FIND(List,G1)))-LOOKUP(BigNum,FIND(List,G1))),B1
)

....confirmed with CONTROL+SHIFT+ENTER.

 

[Guest]

Thanks Max, works great!

 

[Max]

Thanks Max, works great!

Delighted to hear that, Steve !
Thanks for feedback ..

---

 

[Guest]

Assuming in Cell G2 the following data:
AMEX:ARY, AMEX:BDM, AMEX:BHM, AMEX:BPN, AMEX:BWR, AMEX:BXA, AMEX:BXU,
AMEX:CSM, AMEXCV, AMEXMD, AMEXMP, AMEXSA, AMEXSE, AMEXSI,
AMEXSJ, AMEXSK, AMEXSN, AMEXSO, AMEXSP, AMEXSZ, AMEXTY,
AMEX:EST, AMEX:ESY, AMEX:EUM, AMEX:GWM, AMEX:IHM, AMEX:LDB, AMEX:MCP,
AMEX:MDJ, AMEX:MHR, AMEX:MIS, AMEX:MKO, AMEX:MKP, AMEX:MLJ, AMEX:MLN,
AMEX:MLW, AMEX:MNK, AMEX:MNM, AMEX:MPF, AMEX:MPL, AMEX:MTT, AMEX:NKB,
AMEX:NKM, AMEX:NKS, AMEX:NKW, AMEX:NML, AMEX:OGN, AMEXPE, AMEX:RNI,
AMEX:RRM, AMEX:RSM, AMEX:SME, AMEX:SRK, AMEX:UUD, AMEX:UUY, BASE:MER,
BMV:MER, DB:MER, NasdaqNM:ARGB, NasdaqNM:ARQQ, NasdaqNM:ARRB, NasdaqNM:CSJB,
NasdaqNMOTN, NasdaqNMOWT, NasdaqNMWID, NasdaqNMWMT, NasdaqNMWTN,
NasdaqNMWTT, NasdaqNM:LERA, NasdaqNM:LNDU, NasdaqNM:MITT, NasdaqNM:MLMT,
NasdaqNM:MNNY, NasdaqNM:MSPX, NasdaqNM:MTDB, NasdaqNM:MTDW, NasdaqNM:MTNK,
NasdaqNM:MTSM, NasdaqNM:MTSP, NasdaqNM:MTTT, NasdaqNM:MTTX, NasdaqNMDNT,
NasdaqNMGEB, NasdaqNM:SPPX, NasdaqNM:SRDD, NasdaqNM:SRIX, NasdaqNM:SRRR,
NYSE:IEM, NYSE:MER.PRG, NYSE:MER.PRH, NYSE:MER.PRI, NYSE:MER.PRJ,
OTCPK:MERI.Z, OTCPK:MEXZ, NYSE:MER

I just want to return in Cell H2: "NYSE:MER"

That means the formula will:
1) look at text strings that follow "NYSE:" and precede "." and return only
that text that matches that exact criteria.
2) This formula is an extension of the "BigNum" solution that Domenic listed.

Thanks very much!

 

[Guest]

An alternative approach is just to return the last piece of text with "NYSE:"
in it... I noticed that the correct NYSE ticker is always the last one listed
in the cell...

 

[Max]

An alternative approach is just
to return the last piece of text with "NYSE:" in it...
I noticed that the correct NYSE ticker is
always the last one listed in the cell...

Steve,

In the interim, found one possible way:

With Myrna Larson's UDF* implemented in the book
we could put in H2: =MID(G2,xInstrRev(G2,"NYSE:"),99)
and copy H2 down

(the 99 is just an arbitrary number)

*Myrna's UDF is pasted below (for xl97, my ver)

Install the UDF in the *same* book (reqd for UDFs)
that the UDF is going to be used

To install, press Alt+F11, click Insert > Module,
then paste the UDF into the code window
then press Alt+Q to get back to Excel

Here's a sample file with Myrna's UDF implemented:
http://www.savefile.com/files/7035665
Extract_From_Right_MyrnaLarson_UDF.xls

---

'---- UDF by Myrna Larson --------
Function xInstrRev(ByVal Target As String, ByVal Fragment As String, _
Optional StartPos As Long = -1, Optional CompareMode _
As Long = vbBinaryCompare) As Long
'Myrna Larson

Dim Start As Long
Dim PrevStart As Long
Dim LastPossibleStart As Long

If StartPos = -1 Then StartPos = Len(Target)
LastPossibleStart = StartPos + 1 - Len(Fragment)

PrevStart = 0
Do
Start = InStr(PrevStart + 1, Target, Fragment, CompareMode)
If Start = 0 Or Start > LastPossibleStart Then Exit Do
PrevStart = Start
Loop
xInstrRev = PrevStart

End Function
'------ end -------

 

[Max]

we could put in H2: =MID(G2,xInstrRev(G2,"NYSE:"),99)

xInstrRev(G2,"NYSE:")
will return the "rightmost" location of the substring: "NYSE:"
within G2, via searching right-to-left instead of left-to-right

Here's the googled post by Myrna:
http://tinyurl.com/zb97v

---