Hi,

Am Mon, 1 Feb 2021 12:11:05 -0800 (PST) schrieb Tatsujin:

For example, if

myStr = "ant,antique,art,bee,beautiful,bored,chores,dancin g,daytime"

then the output should be:

myStr2 = "--A--,ant,antique,art,--B--,bee, etc.."

then try:

Sub Test()
Dim myStr As String
Dim varData() As Variant
Dim re, match, matches, ptrn
Dim n As Long, i As Long

Set re = CreateObject("vbscript.regexp")
ptrn = "\w+"

myStr = "ant,antique,art,bee,beautiful,bored,chores,dancin g,daytime"
re.pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set matches = re.Execute(myStr)
ReDim Preserve varData(n)
varData(n) = "---" & UCase(Left(matches(1), 1)) & "---"
n = n + 1
For i = 0 To matches.Count - 2
ReDim Preserve varData(n)
If Left(matches(i + 1), 1) = Left(matches(i), 1) Then
varData(n) = matches(i)
n = n + 1
Else
varData(n) = matches(i)
n = n + 1
ReDim Preserve varData(n)
varData(n) = "---" & UCase(Left(matches(i + 1), 1)) & "---"
n = n + 1
End If
Next
ReDim Preserve varData(n)
varData(n) = matches(matches.Count - 1)
myStr = Join(varData, ", ")
Range("A1") = myStr
End Sub


Regards
Claus B.
--
Windows10
Microsoft 365 for business