View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernd P Bernd P is offline
external usenet poster
 
Posts: 806
Default Numbers of repeats of a string

Hello Luciano,

If I understand you correctly, the correct result for your example is:
AG 3
GA 3
AGG 2
AGGA 2
GAG 2
GG 2
GGA 2

You can get this if you array-enter into A3:B9:
=GSort(pfreq(TRANSPOSE(GenSubStrings(A1))),"DA","N S","21")

GSort you will find he
http://www.sulprobil.com/html/sort_vba.html

Pfreq is he
http://www.sulprobil.com/html/pfreq.html

Function GenSubStrings(s As String) As Variant
ReDim v(1 To 10000) As Variant
Dim i As Long, j As Long, k As Long

'Generate all substrings of s with length 2 to Len(s)-1
For i = 2 To Len(s) - 1
For j = 1 To Len(s) - i + 1
k = k + 1
v(k) = Mid(s, j, i)
Next j
Next i

ReDim Preserve v(1 To k) As Variant
GenSubStrings = v
End Function

Regards,
Bernd