Find Data in a String

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

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