This post at vbCity piqued my interest today. I got a bit carried away with providing a solution. In retrospect I think that the use of a simple left$ statement would have solved the member's problem. Anyhow the idea of removing duplicate words from a string appealed to me and after re-working my rather bloated code I came up with the following solution:
Private Function RemoveDuplicateWords(ByVal strVvText As String _
, ByVal strVvDelimiter As String _
) As String
Dim intLvPreviousIndex As Integer: intLvPreviousIndex = 1
Dim intLvIndex As Integer: intLvIndex = InStr(strVvText, strVvDelimiter) + 1
Dim strLvTextSuffix As String: strLvTextSuffix = ""
' Need to delimiter to end of string if it is not already there
strLvTextSuffix = IIf(Right$(strVvText, Len(strVvDelimiter)) = strVvDelimiter _
, "" _
, strVvDelimiter _
)
strVvText = strVvText & strLvTextSuffix
Do While intLvIndex > intLvPreviousIndex
strVvText = Left$(strVvText, intLvIndex - 1) _
& Replace$(Mid$(strVvText, intLvIndex) _
, Mid$(strVvText _
, intLvPreviousIndex _
, intLvIndex - intLvPreviousIndex _
) _
, "" _
)
' Start of previous word
intLvPreviousIndex = intLvIndex
' Start of next word
intLvIndex = InStr(intLvIndex, strVvText, strVvDelimiter) + 1
Loop
' Need to remove delimiter at end of string unless added at beginning of method
strVvText = IIf(Right$(strVvText, Len(strLvTextSuffix)) = strLvTextSuffix _
, Left$(strVvText, Len(strVvText) - Len(strLvTextSuffix)) _
, strVvText _
)
RemoveDuplicateWords = strVvText
End Function
I am sure there must be a better solution to the problem. Any takers?
Note that my solution only caters for a single word separator. Full stops, commas etc. could not be catered for without multiple calls to the function. Also the the first occurance is always the one left behind.