ExcelBanter - substring operation

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 = "again1again2again3again4again5again6again7aga in8"
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 = "again1again2again3again4again5again6again7aga in8"
strFind = "again"
lngInst = 4
strFinal = WorksheetFunction.Substitute(strLook, strFind, "~", lngInst)
lngPos = InStr(1, strFinal, "~")
strWhole = Mid(strLook, lngPos, Len(strFind))
End Sub

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.

"jana" wrote in message
oups.com...
hi ,
is there a way to find the n'th instance of a substring in its
parent string ?

regards,
jana