Remove duplicates of specific word but leave the first instance in a string-CodePudding

I have this string of word:

Two Thousand and Two Hundred and Point Thirty

I need a function to help me achieve:

Two Thousand and Two Hundred Point Thirty

I have tried using RemoveDuplicates function as per below but the result is:

Two Thousand Two Hundred Point Thirty

The result of the code below is:

Two Thousand Two Hundred Point Thirty

The code as below:

Function RemoveDuplicates(rng as Range) As String
    Dim dict As Object
    Dim var As Variant, v As Variant

    Set dict = CreateObject("Scripting.Dictionary")

    var = Split(rng.Value," and ")

    For each v in var
        If Not dict.Exists(v) Then
            dict.Add v, v
        End If
    Next v

    RemoveDuplicates = Join(dict.Keys, " ")
End Function

I felt that there is a need to put in a specific word as the delimiter as I would not want the double TWO to be deleted when i use the function. All I want is to remove all the "and" except for the 1st instance of the word but at the same time not wanting the code to think that the "and" in thousand is counted as the first instance.

Thank you very much for the help!

CodePudding user response：

So I don't think you are far off in your approach. I have changed your function instead to take the string as an argument. Here is my attempt (probably not the best way to do this, but gets the desired result) :

Option Explicit

Function RemoveDuplicates(stringToRemove As String) As String
    
    Dim arr As Variant
    Dim i As Long, andCheck As Integer
    Dim tempStr As String
    
    arr = Split(stringToRemove, " ")
    
    For i = LBound(arr) To UBound(arr)
        ' condition to be met if word is "and"
        If arr(i) = "and" And andCheck >= 1 Then
            ' just continue, do not process
        ElseIf arr(i) = "and" Then
            tempStr = tempStr & arr(i) & " "
            andCheck = andCheck   1
        Else
            ' rebuild string
            tempStr = tempStr & arr(i) & " "
        End If
    Next i
    
    RemoveDuplicates = Left(tempStr, Len(tempStr) - 1)
    
End Function

Running a test routine :

Sub test()
    
    Dim inputStr As String: inputStr = "Two Thousand and Two Hundred and Point Thirty"
    
    Debug.Print RemoveDuplicates(inputStr)
    
End Sub

Returns : Two Thousand and Two Hundred Point Thirty

CodePudding user response：

it's much easier with Instr() function

Option Explicit

Function RemoveDuplicates(rng As Range) As String
    Dim firstAndPos As Long
    firstAndPos = InStr(1, rng.Value, " and ", vbTextCompare) ' locate the position of the first instance of " and "
        RemoveDuplicates = Left$(rng.Value, firstAndPos) & Mid$(rng.Value, firstAndPos   Len(" and ")) ' concatenate the part of string preceding the first instance with what follows it
End Function

But should you want and stick to the Dictionary approach, then iterate through var elements from the 2nd one on

Option Explicit

Function RemoveDuplicates(rng As Range) As String
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Dim var As Variant
    var = Split(rng.Value, " and ")

        Dim iv As Long
        For iv = LBound(var)   1 To UBound(var)
            dict.Add var(iv), 0
        Next
    
            RemoveDuplicates = var(LBound(var)) & " " & Join(dict.Keys, " and ")
End Function