UDF Get the position of the first character of sub-string of string

[userxyz]

Hello, new to vba and I need help writting a UDF

I need a macro to get the position of the first character of a repeated sub-string of a string

If cell value is "The green cat something1 the green cat something2 in ... green cat somethingM"

Sub-string is the nth instance of "green cat"

How to get the position number of the g in the full string

Thanks for any help on this

 

[VBA Geek]

something along these lines i assume is what you are after

Excel 2013/2016
	A
1	The green cat something1 the green cat something2 in ... green cat somethingM"
2	green cat
3	58
Sheet1

Cell Formulas
Range	Formula
A3	=GetNthPosition(A1,A2,3)

Function GetNthPosition(strToSearch As String, _
                        strMatch As String, _
                        nTh As Long) As Long
Dim j As Long, i As Long
j = 0
i = InStr(1, strToSearch, strMatch)
Do Until i = 0
    j = j + 1
    If j = nTh Then GetNthPosition = i: Exit Function
    i = InStr(1 + i, strToSearch, strMatch)
Loop
GetNthPosition = 0
End Function

 

[userxyz]

Thank you! that is what I needed

May I ask a follow up question?

Say the sub-string was not repeated just a random sub-string of a string how would I then get the position of the first character of a NON- repeated sub-string of a string

Thank you again for you

 

[VBA Geek]

You would use 1 as the third argument of the above function.

Thank you! that is what I needed

May I ask a follow up question?

Say the sub-string was not repeated just a random sub-string of a string how would I then get the position of the first character of a NON- repeated sub-string of a string

Thank you again for you

 

[userxyz]

Thank you again