InStr (3rd instance of character required)

[elliotthope]

I use the following code to get the first and second instances of a "/" character. I cannot get my head around how to get the position of the third instance. Does anybody have any ideas??

Thanks,
Elliott


iUPC = "123-7754LF-(A/S red Top)-T19/97876564"
'get number of instances
xTimes = 0
xTimes = Len(iUPC) - Len(Replace(iUPC, "/", ""))
'get position of characters
xInstance1 = InStr(1, iUPC, "/")
xInstance2 = InStr(InStr(1, iUPC, "/") + 1, iUPC, "/")

 

[Brianwarnock]

Use the knowledge where the previous instance is to start the instr


xInstance2 = InStr(InStr(1, iUPC, "/") + 1, iUPC, "/")

Becomes


xInstance2 = InStr(xinstance1 + 1, iUPC, "/")

This to find the third use xinstance2 +1 as the start

Brian

 

[stopher]

You could use the Replace function to find the first n-1 instances and replace them with some alternative character. Then use Instr to find the nth instance:

InStr(Replace( iUPC,"/","z",1,n-1),"/")

Chris

 

[nanscombe]

Will there always just be 3 x "/"?

If it is the position of the last instance that you require you could use the InStrRev() function.

It works in a similar fashion to InStr() but, as the name suggests, in reverse.

InStrRev Function

Returns the position of an occurrence of one string within another, from the end of string.

Syntax

InstrRev(stringcheck, stringmatch [, start ] [, compare ] )

...

 

[elliotthope]

All great responses everybody. I went with this as requested by Brian in the end:

xinstance2 = InStr(xinstance1 + 1, iUPC, "/")
xinstance3 = InStr(xinstance2 + 1, iUPC, "/")
xinstance4 = InStr(xinstance3 + 1, iUPC, "/")

So simple, yet i couldn't think of it yesterday!

Thankyou all.

Elliott