get the the character instance of the 1st character of a sub-sting in a string

[capson]

Hello,

I need to get the character instance of the 1st character of a sub-string in a string

I can get the character instance of the entire string but not the 1st character of a sub-string in a string

Ex
ref_value.value = sjdhfss jj sd (ppo nns) GGG
sub-string = sd (ppo nns)

the first instance of substring = s
its instance = 4

Thanks

Function COUNTTEXT(ref_value As Range, ref_string As String) As Long


Dim i As Integer, count As Integer


count = 0
If Len(ref_string) <> 1 Then COUNTTEXT = CVErr(xlErrValue): Exit Function
For i = 1 To Len(ref_value.value)
    If Mid(ref_value, i, 1) = ref_string Then count = count + 1
Next


COUNTTEXT = count


End Function

 

[VBA Geek]

Does this do what you want?

Excel 2013/2016
	D	E	F	G
5	sjdhfss jj sd (ppo nns) GGG			4
6	sd (ppo nns)
Sheet1

Cell Formulas
Range	Formula
G5	=INSTR_SUBSTR(D5,D6)

Function INSTR_SUBSTR(ByVal sMain As String, sSub As String) As Long
Dim iSubStrPos As Long
Dim firstChar  As String
Dim iCounter   As Long
Dim iPos       As Long


iSubStrPos = InStr(1, sMain, sSub)


If iSubStrPos = 0 Then
    INSTR_SUBSTR = 0
Else
    firstChar = Left$(sSub, 1)
    sMain = Left$(sMain, iSubStrPos - 1)
    
    iPos = InStr(1, sMain, firstChar)
    iCounter = 0
    Do Until iPos = 0
        iCounter = iCounter + 1
        iPos = InStr(1 + iPos, sMain, firstChar)
    Loop
    INSTR_SUBSTR = iCounter + 1
End If
End Function

 

[capson]

Yes indeed it does! Thank you

 

[shg]

Or a formula ...

	A​	B​	C​
2​	sjdhfss jj sd (ppo nns) GGG	4​	B2: =LEN(LEFT(A2, FIND(A3, A2))) - LEN(SUBSTITUTE(LEFT(A2, FIND(A3, A2)), LEFT(A2), ""))
3​	sd (ppo nns)