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 ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]sjdhfss jj sd (ppo nns) GGG[/td][td="bgcolor:#CCFFCC"]

4​

[/td][td]B2: =LEN(LEFT(A2, FIND(A3, A2))) - LEN(SUBSTITUTE(LEFT(A2, FIND(A3, A2)), LEFT(A2), ""))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]sd (ppo nns)[/td][td][/td][td][/td][/tr]
[/table]