substring operation

[jana]

hi ,
is there a way to find the n'th instance of a substring in its
parent string ?

regards,
jana

 

[Zack Barresse]

Hi, here are a couple of examples that may help ...


Sub FindReplaceNthVal()
Dim strLook As String
Dim strFind As String
Dim lngInst As Long
Dim strFinal As String
strLook = "again1again2again3again4again5again6again7again8"
strFind = "again"
lngInst = 4
strFinal = WorksheetFunction.Substitute(strLook, strFind, "", lngInst)
MsgBox strFinal
End Sub
Sub FindNthPosition()
Dim strLook As String
Dim strFind As String
Dim lngInst As Long
Dim lngPos As Long
Dim strFinal As String
Dim strWhole As String
strLook = "again1again2again3again4again5again6again7again8"
strFind = "again"
lngInst = 4
strFinal = WorksheetFunction.Substitute(strLook, strFind, "~", lngInst)
lngPos = InStr(1, strFinal, "~")
strWhole = Mid(strLook, lngPos, Len(strFind))
End Sub


HTH