Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Data in a String
Within some code in a macro I have a variable
called "Temp" that can hold data like: BFYS: 94FUND:FUND:0160X4 or BFYS: 01 02FUND:FUND:0160X4 I want to parse out (capture) two variables: 1. The characters between the first colon and the first "FUND" word. 2. The characters after the third colon. In the frist example variable one would equal "94" and two would be "0160X4." In the second example they would be "01 02" and "0160X4." I tried using the "Find" operator, but couldn't get it to work. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Data in a String
temp = "BFYS: 94FUND:FUND:0160X4" iloc1 = Instr(1,Temp,":",vbTextCompare) iloc2 = Instr(1,Temp,"fund",vbTextCompare) ? Mid(temp,iloc1+1,iloc2-iloc1-1) 94 iloc3 = Instr(instr(iloc1+1,temp,":",vbTextCompare)+1,Temp ,":",vbTextcompare) ? right(temp,len(temp)-iloc3) 0160X4 --------------- temp = "BFYS: 01 02FUND:FUND:0160X4" iloc1 = Instr(1,Temp,":",vbTextCompare) iloc2 = Instr(1,Temp,"fund",vbTextCompare) ? Mid(temp,iloc1+1,iloc2-iloc1-1) 01 02 iloc3 = Instr(instr(iloc1+1,temp,":",vbTextCompare)+1,Temp ,":",vbTextcompare) ? right(temp,len(temp)-iloc3) 0160X4 -- Regards, Tom Ogilvy "Bill" wrote in message ... Within some code in a macro I have a variable called "Temp" that can hold data like: BFYS: 94FUND:FUND:0160X4 or BFYS: 01 02FUND:FUND:0160X4 I want to parse out (capture) two variables: 1. The characters between the first colon and the first "FUND" word. 2. The characters after the third colon. In the frist example variable one would equal "94" and two would be "0160X4." In the second example they would be "01 02" and "0160X4." I tried using the "Find" operator, but couldn't get it to work. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Data in a String
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Data in a String
Worked great. Thanks for assistance!
I'll add "Instr" function to my "knowledge base." -----Original Message----- temp = "BFYS: 94FUND:FUND:0160X4" iloc1 = Instr(1,Temp,":",vbTextCompare) iloc2 = Instr(1,Temp,"fund",vbTextCompare) ? Mid(temp,iloc1+1,iloc2-iloc1-1) 94 iloc3 = Instr(instr(iloc1+1,temp,":",vbTextCompare) +1,Temp,":",vbTextcompare) ? right(temp,len(temp)-iloc3) 0160X4 --------------- temp = "BFYS: 01 02FUND:FUND:0160X4" iloc1 = Instr(1,Temp,":",vbTextCompare) iloc2 = Instr(1,Temp,"fund",vbTextCompare) ? Mid(temp,iloc1+1,iloc2-iloc1-1) 01 02 iloc3 = Instr(instr(iloc1+1,temp,":",vbTextCompare) +1,Temp,":",vbTextcompare) ? right(temp,len(temp)-iloc3) 0160X4 -- Regards, Tom Ogilvy "Bill" wrote in message ... Within some code in a macro I have a variable called "Temp" that can hold data like: BFYS: 94FUND:FUND:0160X4 or BFYS: 01 02FUND:FUND:0160X4 I want to parse out (capture) two variables: 1. The characters between the first colon and the first "FUND" word. 2. The characters after the third colon. In the frist example variable one would equal "94" and two would be "0160X4." In the second example they would be "01 02" and "0160X4." I tried using the "Find" operator, but couldn't get it to work. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find String in another string - only between spaces | Excel Worksheet Functions | |||
Find Many String options in ONE String | Excel Worksheet Functions | |||
Macro to find text string in a column and paste data in another | Excel Discussion (Misc queries) | |||
find a string inside another string | Excel Programming | |||
Wish to parse through a text string to find data | Excel Programming |