Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
Here's one crack at this ..
Put in say, H1: =IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{"NYSE:,";"NasdaqNM:,";"NasdaqS C:,";"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 Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveC" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
Put in say, H1:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,{"NYSE:,";"NasdaqNM:,";"NasdaqS C:,";"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,"")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
A sample implementation is available at:
http://www.savefile.com/files/5209535 Extract complete substring if keystring found.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
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! In article , SteveC wrote: 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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
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(",",G1&",",LOOKUP(BigNum,FIND(List,G1)))-LOOKUP(BigNum,FIND(List,G1)))),B1,(MID(G1,LOOKUP(B igNum,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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text By Priority with With Certain Criteria
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... |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
In article ,
SteveC wrote: 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)),FI ND(",",G1&",",LOOKUP(BigNum, FIND(List,G1)))-LOOKUP(BigNum,FIND(List,G1)))),B1,(MID(G1,LOOKUP(B igNum,FIND(L ist,G1)),FIND(",",G1&",",LOOKUP(BigNum,FIND(List,G 1)))-LOOKUP(BigNum,FIND(List ,G1))))) =IF(OR(ISNUMBER(FIND(List,C2))),MID(C2,LOOKUP(BigN um,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! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text By Priority with With Certain Criteria
See other thread...
In article , SteveC wrote: 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... |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveC" wrote in message ... 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
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(BigN um,FIND(List,G1)),FIND( ",",G1&",",LOOKUP(BigNum,FIND(List,G1)))-LOOKUP(BigNum,FIND(List,G1))),B1 ) ....confirmed with CONTROL+SHIFT+ENTER. In article , Domenic wrote: 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(",",G1&",",LOOKUP(BigNu m, FIND(List,G1)))-LOOKUP(BigNum,FIND(List,G1)))),B1,(MID(G1,LOOKUP(B igNum,FIND (L ist,G1)),FIND(",",G1&",",LOOKUP(BigNum,FIND(List,G 1)))-LOOKUP(BigNum,FIND(Li st ,G1))))) =IF(OR(ISNUMBER(FIND(List,C2))),MID(C2,LOOKUP(BigN um,FIND(List,C2)),FIND( ",",C2&",",LOOKUP(BigNum,FIND(List,C2)))-LOOKUP(BigNum,FIND(List,C2))),B2 ) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
Thanks Max, works great!
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Text with With Certain Criteria
"SteveC" wrote:
Thanks Max, works great! Delighted to hear that, Steve ! Thanks for feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Trim Function with Matching Text with With Certain Criteria
Assuming in Cell G2 the following data:
AMEX:ARY, AMEX:BDM, AMEX:BHM, AMEX:BPN, AMEX:BWR, AMEX:BXA, AMEX:BXU, AMEX:CSM, AMEX:DCV, AMEX:DMD, AMEX:DMP, AMEX:DSA, AMEX:DSE, AMEX:DSI, AMEX:DSJ, AMEX:DSK, AMEX:DSN, AMEX:DSO, AMEX:DSP, AMEX:DSZ, AMEX:DTY, 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, AMEX:PPE, 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, NasdaqNM:DOTN, NasdaqNM:DOWT, NasdaqNM:DWID, NasdaqNM:DWMT, NasdaqNM:DWTN, NasdaqNM:DWTT, 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, NasdaqNM:PDNT, NasdaqNM:PGEB, 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! |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Trim Function with Matching Text with With Certain Criteria
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... |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Trim Function with Matching Text with With Certain Criteria
"SteveC" wrote:
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... I just want to return in Cell H2: "NYSE:MER" 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- '---- 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 ------- |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Trim Function with Matching Text with With Certain Criteria
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Matching 2 text columns | Excel Discussion (Misc queries) | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Text Wrapping | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |