multiple occurence of a particular character in multiple strings inside cell

Question

Function FindN(sFindWhat As String, _
sInputString As String, N As Integer) As Integer
Dim J As Integer
Application.Volatile
FindN = 0
For J = 1 To N
FindN = InStr(FindN + 1, sInputString, sFindWhat)
If FindN = 0 Then Exit For
Next
End Function

Hi, I was trying to get the multiple occurrences of a particular character inside a sentence and I could get that done with the above code, but I was getting that in different cells based on the nth number that I am giving in the formula. But I need all the occurrences of that particular character in that sentence and all that need to be found in a single cell only. Please help me how I can alter the above code to get that sort of result. Please advise.

Answer 1

Get Substring Positions (UDF)

Function FindM( _
    ByVal FindString As String, _
    ByVal WithinString As String, _
    Optional ByVal Delimiter As String = " ") _
As String

    Dim fLen As Long: fLen = Len(FindString)
    Dim fPos As Long: fPos = InStr(1, WithinString, FindString)
    
    Do Until fPos = 0
        FindM = FindM & Delimiter & fPos
        fPos = InStr(fPos + fLen, WithinString, FindString)
    Loop
    
    If Len(FindM) > 0 Then FindM = Mid(FindM, Len(Delimiter) + 1)
    
End Function

If cell A1 contains...

I WANT A banana.

... the results for the function are...

=FindM("a",A1)       11 13 15
=FindM("n",A1)       12 14
=FindM("a",A1,", ")  11, 13, 15
=FindM("n",A1,", ")  12, 14
=FindM("a",A1,"-")   11-13-15
=FindM("n",A1,"-")   12-14