Remove part of a string before adding to New Collection

**jordan2322** · 07-10-2012, 07:50 PM

G'day everyone,

Im using the following code to create a validation list. My question is this - how could I modify the string value to exclude a specified term.
I.e. If the cell.value found makes it thru to the line

                 ValUnique1.Add Cell.Value, CStr(Cell.Value)

and this value is "Fld Coordinator OH", then could i include an extra line before adding it to the collection so that if it contains "Fld Coordinator" then this term is removed, leaving only "OH" as the value to be added to the collection?

Dim Cell As Range
Dim ValUnique1 As New Collection
Dim rStr As String
Dim i As Long


On Error Resume Next
For Each Cell In Sheets(1).Range("A3:A3000")
    If Not IsNumeric(Cell) Then
        If Not Cell.Value Like "*Construction*" And Not Cell.Value Like "*BI*" Then
            If Not Cell.Offset(1, 0).Value Like "*Construction*" And Not Cell.Offset(1, 0).Value Like "*BI*" Then
                ValUnique1.Add Cell.Value, CStr(Cell.Value)
            End If
        End If
    End If
Next Cell

Thanks heaps

**vandan_tanna** · 07-10-2012, 09:02 PM

may be something like this...

Dim Cell As Range
Dim ValUnique1 As New Collection
Dim rStr As String
Dim i As Long
Dim newStr As String
Dim rmStr As String

rmStr = "Fld Coordinator"

On Error Resume Next
For Each Cell In Sheets(1).Range("A3:A3000")
    If Not IsNumeric(Cell) Then
        If Not Cell.Value Like "*Construction*" And Not Cell.Value Like "*BI*" Then
            If Not Cell.Offset(1, 0).Value Like "*Construction*" And Not Cell.Offset(1, 0).Value Like "*BI*" Then
                'ValUnique1.Add Cell.Value, CStr(Cell.Value)
                newStr = Trim(Right(Cell.Value, Len(Cell.Value) - IIf(InStr(1, Cell.Value, rmStr) >= 1, Len(rmStr), 0)))
                ValUnique1.Add newStr, newStr
            End If
        End If
    End If
Next Cell

**jordan2322** · 07-11-2012, 01:06 AM

thank you for your reply vandan, this works great.
My only question is what if i wanted it to be more broad, such that the user could define any term they wanted for it to exclude, and it could determine where in the string the term is and is able to remove it.
an example could be

rmStr = Collar
eg.1
Cell.Value = 'Blue Collar'
newStr = Blue
eg.2
Likewise if Cell.value = Collar Excel
newStr = Excel

regards

**jindon** · 07-11-2012, 01:18 AM

One way

NewStr = Trim$(Replace(Cell.Value, myStr,""))

**vandan_tanna** · 07-11-2012, 10:03 AM

I like jindon's way. it is a bit more neater than mine.

One suggestion I have is to may be have an array with a list of string you want to remove and then processing the cell value through it to remove all unwanted strings. This could be done in a separate function.