Hi all,

This is a VBA function i have right now which i use for removing multiple instances of a word in the cell .. it removes all the multiple instances except the last one ..


Function UniqInCell(ByVal txt As String) As String
Dim temp, m As Object, n As Long
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "([,;:\.\?] ?)"
txt = .Replace(txt, " ")
.IgnoreCase = True
.Global = False
.Pattern = "\b((.{2,})((?!e?s)|(e?s)?)) .*(\2(e?s)?)"
Do While (.test(txt))
Set m = .Execute(txt)(0)
If Not .test(txt) Then Exit Do
Dim i As Integer
i = 0

txt = Application.Replace(txt, _
m.firstindex + 1, Len(m.submatches(0)), "")


Loop
End With
UniqInCell = Application.Trim(txt)
End Function


I have found this code on this site and has served my purpose so far ..

I want someone who can help me to add a rule that it doesnt remove multiple instances of words which are less < = 3 characters .. ( I want it to retain the words like the , a , an etc. )

thanks in advance