Quickest way to search comma delimited string?

[Deutz]

I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found.

For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years.

I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?

Thanks

 

[Deutz]

Thanks for your efforts Peter.

I'll have a go at it in VBA.

 

[mikerickson]

You might look at these UDF's.
The first one returns True/False if a string meets the conditions described in the OP with strings like "2004, 2005, 2004"
The second does the same for strings like "01/03/2004, 01/08/2005"

Function isSoundYear(aString As String) As Boolean
    Dim oneSubString As Variant, newStr As String
    isSoundYear = True
    For Each oneSubString In Array(2004, 2005): Rem adjust
        newStr = Application.Substitute(aString, oneSubString, vbNullString)
        isSoundYear = isSoundYear And ((Len(aString) - Len(newStr)) <= Len(oneSubString))
        aString = newStr
    Next oneSubString
    isSoundYear = isSoundYear And (Trim(Application.Substitute(aString, ",", vbNullString)) = vbNullString)
End Function

Function isSoundDate(aString As String) As Boolean
    Dim startDate As Date
    Dim myArray(1 To 24) As String, i As Long
    Dim oneDateString As Variant
    Dim newStr As String
    
    startDate = DateSerial(2004, 1, 1): Rem adjust
    For i = 0 To UBound(myArray) - 1
        myArray(i + 1) = Format(DateSerial(Year(startDate), Month(startDate) + i, 1), "dd/mm/yyyy")
    Next i
    
    isSoundDate = True
    For Each oneDateString In myArray
        newStr = Application.Substitute(aString, oneDateString, vbNullString)
        isSoundDate = isSoundDate And ((Len(aString) - Len(newStr)) <= Len(oneDateString))
        aString = newStr
    Next oneDateString
    isSoundDate = isSoundDate And (Trim(Application.Substitute(aString, ",", vbNullString)) = vbNullString)
End Function

 

[Deutz]

Thanks for the UDFs.

I'll take a closer look at them tomorrow and see if I can implement into my project.

Regards

Michael