A solution with Regular Expression
![]()
Sub jec() Dim ar, j As Long, y As Long ar = Range("a1").CurrentRegion ReDim ary(UBound(ar), 0) With CreateObject("VBScript.RegExp") .Global = True .Pattern = "([A-Z]{3})(?=.*?\1.*$)" For j = 1 To UBound(ar) If .test(ar(j, 1)) Then ary(y, 0) = ar(j, 1) y = y + 1 End If Next Range("G1").Resize(y) = ary End With End Sub
Bookmarks