I have kindly been provided the following Formulas, each intended to remove duplicate text within a individual cells in a selected column. I would appreciate if these were incorporated in Sub macro form, which I know how to use (insert in Module and access and access and use through the Macro menu).
More specifically, issue is as follows:
Cells in a selected column contain duplicate text that needs to be removed as follows, :
- apple apple other text banana banana banana other text banana other text apple
Result after Macro should be:
- apple other text banana other text banana other text apple
This means Macro needs to delete ONLY all duplicate text that is located consequent (next) to each other, being repetitive text by error, rather than delete all duplicate text in the cell. Meaning, if text is not a repetitive duplicate in sequence (such as: boat hull, boat hull) then it should be left as is, despite found multiple times within the cell.
Function 1 expected to do this, is as follows:
Function 2 expected to do the same job:
Well, sorry to say I did not manage to test and use any of the functions above. I am a simple user that only knows how to insert a macro in a module and use that macro from the Macro menu to process data in cells of a selected column. The above functions, as useful as I trust they are, they are of no use to me. They have to be incorporated in a Sub, which thereafter I know how to use.
If someone "out there" can spare some time to apply these functions within a macro I can use, this, needless to say, will be of great help to me and to many other "non-experts" looking for such a solution. Thanks.
.
More specifically, issue is as follows:
Cells in a selected column contain duplicate text that needs to be removed as follows, :
- apple apple other text banana banana banana other text banana other text apple
Result after Macro should be:
- apple other text banana other text banana other text apple
This means Macro needs to delete ONLY all duplicate text that is located consequent (next) to each other, being repetitive text by error, rather than delete all duplicate text in the cell. Meaning, if text is not a repetitive duplicate in sequence (such as: boat hull, boat hull) then it should be left as is, despite found multiple times within the cell.
Function 1 expected to do this, is as follows:
Code:
Option Explicit
Private colOutput As New Collection
Public Function split_cell(str As String) As String
Dim strInput() As String
Dim iArrayCount As Integer
Dim iCollectionCount As Integer
Dim strWord As String
strInput = Split(Range("a1"), Chr(32))
For iArrayCount = 0 To UBound(strInput)
' Get the word
strWord = strInput(iArrayCount)
If Not KeyExist(strWord) Then
If colOutput.Count = 0 Then
colOutput.Add strWord, strWord
Else
colOutput.Add strWord, strWord, , colOutput.Count
End If
End If
Next iArrayCount
For iCollectionCount = 1 To colOutput.Count
If iCollectionCount = 1 Then
split_cell = split_cell & colOutput(iCollectionCount)
Else
split_cell = split_cell & Chr(32) & colOutput(iCollectionCount)
End If
Next iCollectionCount
End Function
Public Function KeyExist(strKeyName As String) As Boolean
Dim i As Integer
KeyExist = False
If Not colOutput Is Nothing Then
If colOutput.Count = 0 Then Exit Function
For i = 1 To colOutput.Count
If colOutput.Item(i) = strKeyName Then
KeyExist = True
Exit For
End If
Next i
End If
End FunctionFunction 2 expected to do the same job:
Code:
Function UniTerms(rngString As Range, Optional strDelim As String = " ") As String
vW = Split(rngString, strDelim)
U = vW(0)
For j = 1 To UBound(vW)
If vW(j) <> vW(j - 1) Then U = U & strDelim & vW(j)
Next
UniTerms = U
End FunctionWell, sorry to say I did not manage to test and use any of the functions above. I am a simple user that only knows how to insert a macro in a module and use that macro from the Macro menu to process data in cells of a selected column. The above functions, as useful as I trust they are, they are of no use to me. They have to be incorporated in a Sub, which thereafter I know how to use.
If someone "out there" can spare some time to apply these functions within a macro I can use, this, needless to say, will be of great help to me and to many other "non-experts" looking for such a solution. Thanks.
.
