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

 

[Blade Hunter]

You could use replace and check the resulting string like this:

Sub TestForYear()
Dim FoundYear As Boolean
FoundYear = False
For X = 2001 To 2008
    If Range("A1").Text <> Replace(Range("A1").Text, X, "") Then FoundYear = True
Next
If FoundYear Then
    'Whatever you want to happen if it exists
Else
    'Whatever you want to happen ig it doesn't exist
End If
End Sub

Obviously you could put a loop in there to poll through the range quite easily.

 

[Deutz]

Thanks Blade Hunter.

I tested your suggestion but it doesn't quite do what I want. For instance, if a cell contains the years (2002, 2003, 2005) then the sub returns false for 2001, true for 2002, true for 2003, false for 2004, true for 2005, false for 2006, false for 2007 and false for 2008.

So it works if I want to know if any of my years in the range 2001 to 2009 are in the cell but it doesn't identify any inappropriate values in the cell that are outside of my range of years.

 

[Peter_SSs]

Deutz

Welcome to the MrExcel board!

1. Are the years actually enclosed in () like your samples?

2. In each cell, if there are multiple years, do they alway go from smallest to largest like your samples?

If 'yes' to both, then does this do what you want?

Excel Workbook
	A	B
1	(2001, 2005, 2006, 2007, 2008)	TRUE
2	(2006, 2007)	TRUE
3	(2001, 2008)	TRUE
4	(2001, 2004, 2008, 2009)	FALSE
5	(2004)	TRUE
6	(2008)	TRUE
7	(1999)	FALSE
8	(1999, 2002)	FALSE
Check Years (1)

If the answer to 1 is 'no' but 2 is still 'yes', then it's a bit simpler:

Excel Workbook
	A	B
1	2001, 2005, 2006, 2007, 2008	TRUE
2	2006, 2007	TRUE
3	2001, 2008	TRUE
4	2001, 2004, 2008, 2009	FALSE
5	2004	TRUE
6	2008	TRUE
7	1999	FALSE
8	1999, 2002	FALSE
Check Years (2)

 

[Deutz]

Thanks Peter. Much appreciated.

Your second solution (no brackets) works a treat and is elegantly simple!


Sorry, but I forgot to mention that I also want the formula to return TRUE if the cell is empty. Would you just nest your formula in an IF or something?
=======================================================

I also have another problem, similar to this first one but a little more complex and I wonder if you could help.

I have another spreadsheet that also has columns of comma delimited values but this time they are dates.

One column has dates which are always the first day of a month and can be for any month in a year range which I know. For instance, a cell may contain: 01/02/07, 01/06/08, 01/07/08, 1/4/09 and the year range may be between 2007 and 2009.

I want to return FALSE if the cell contains a delimited string which is not a valid first day of the month that falls within the year range.

I want to return TRUE if every string in the cell is a valid first day of the month that falls within the year range OR if the cell is empty.
-----------------------------------------------------------------------

The other column has dates which are always the last day of a month and can be for any month in a year range which I know. For instance, a cell may contain: 31/01/08, 30/09/08, 31/01/09, 31/12/09 and the year range may be between 2001 and 2010.

I want to return FALSE if the cell contains a delimited string which is not a valid last day of the month that falls within the year range.

I want to return TRUE if every string in the cell is a valid last day of the month that falls within the year range OR if the cell is empty.
-----------------------------------------------------------------------

Don't really know how I'd approach this except perhaps in some cumbersome VBA loop.


Thanks again

 

[Deutz]

Peter, for my first question regarding comma delimited years, I placed your formula in an IF and it works fine if the cell is empty:

=IF(A1 <> "", AND(LEFT(A1,4)+0>=2001,RIGHT(A1,4)+0<=2008), TRUE)

The only other thing I noticed was that it returns #VALUE! error if there is a non number in the cell. Is there any way to transform this in the formula to return FALSE.

Thanks

 

[Deutz]

I'm gradually solving my problems and have figured out how to return false if #VALUE! is returned.

I used ISERROR and that returns FALSE as required:

=IF(ISERROR(AND(LEFT(A1,4)+0>=2001,RIGHT(A1,4)+0<=2008)), FALSE, IF(A1 <> "", AND(LEFT(A1,4)+0>=2001,RIGHT(A1,4)+0<=2008)))

I haven't solved my second question below on comma delimited start and end dates yet so any suggestions would be appreciated.

Thanks

 

[Peter_SSs]

I'm gradually solving my problems and have figured out how to return false if #VALUE! is returned.

I used ISERROR and that returns FALSE as required:

=IF(ISERROR(AND(LEFT(A1,4)+0>=2001,RIGHT(A1,4)+0<=2008)), FALSE, IF(A1 <> "", AND(LEFT(A1,4)+0>=2001,RIGHT(A1,4)+0<=2008)))

Could you give some examples of what could be in the cell if it is not empty and not a 'standard' string of years. Depending on those samples, there may possibly be a simpler formula.

I haven't solved my second question below on comma delimited start and end dates yet so any suggestions would be appreciated.

Your second question is not clear to me. Again, some varied examples (keep the strings as short as you can while still showing the idea) of data and expected results should help clarify the problem.

Have you tried Excel jeanie or RichardSchollar’s beta HTML Maker? They make posting your samples easy.

 

[Deutz]

Hi <?xml:namespace prefix = st1 ns = "urn:schemas:contacts" /><st1:GivenName w:st="on">Peter</st1:GivenName>,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Sorry, I’ll try to be a bit clearer.<o></o>

The purpose of the first formula is to find out if there are only comma delimited years in the cell which are between two given years and to flag anything else as false, including letters, numbers and symbols or a combination of these. There may be from 0 to 12 years in a cell. The years can be in any order but can’t be repeated.<o></o>
<o></o>
For example, supposing that the year range was 2004 to 2005, I would want to flag the following cell contents thusly:

[2001]................ FALSE (2001 not in year range)
[2004, 2002, 2005].....FALSE (2002 not in the year range)<o></o>
[2004, 2005, 2004].....FALSE (2004 repeated)
[2004, 20a7, 2005].....FALSE (20a7 not a valid year in year range)
[&!, 333, *C!E37]......FALSE (not valid years in year range)<o></o>
[Sderwer werr2#].......FALSE (not valid years in year range)<o></o>
[2004, 2005]...........TRUE (all valid years in year range)<o></o>
[2005].................TRUE (2005 valid year in year range)
[ ]....................TRUE (Ok to have nothing in cell)<o></o>
<o></o>
Note that the square brackets in this example are not part of the cell contents<o></o>
=============================================================<o></o>
<o></o>
My second more complex problem with comma delimited dates:<o></o>
<o></o>
I have a column of comma delimited dates that should all be the first day of the month in a given year range. <o></o>
<o></o>
Let’s suppose the year range is 2004 to 2005 and as there are 24 months in those years, in this example there could be from 0 to 24 dates in the cell and they can be in any order but can’t be repeated.<o></o>
<o></o>
I would want to flag the following cell contents thusly:<o></o>
<o></o>
[01/03/2004, 02/08/2005]....FALSE (02/08/2005 not first day of month)
[01/03/2004, 01/08/2006]....FALSE (01/08/2006 not in year range)<o></o>
[01/03/2004, 01/03/2004]....FALSE (01/03/2004 repeated)
[01/54/2004, 01/08/2005]....FALSE (01/54/2004 not a valid date in year range)<o></o>
[dsfaf, 01/08/2005].........FALSE (dfsaf not a valid date in year range)<o></o>
[&!, 333, *C!E37]...........FALSE (Not valid dates in year range)<o></o>
[01/03/2004, 01/08/2005]....TRUE (Dates are first day of month in year range)<o></o>
[ ].........................TRUE (Ok to have nothing in cell)<o></o>
<o></o>
Note that the square brackets in this example are not part of the cell contents<o></o>
==========================================================<o></o>
<o></o>
The other column of comma delimited dates is pretty much the same problem as this one above except with the dates having to be the last day of the month instead of the first.<o></o>
<o></o>
Let’s suppose the year range is 2004 to 2005 and as there are 24 months in those years, in this example there could be from 0 to 24 dates in the cell and they can be in any order but can’t be repeated.<o></o>
<o></o>
I would want to flag the following cell contents thusly:<o></o>
<o></o>
[31/03/2004, 30/08/2005]....FALSE (30/08/2005 not last day of month)<o></o>
[31/03/2004, 31/08/2006]....FALSE (31/08/2006 not in year range)<o></o>
[31/03/2004, 31/03/2004]....FALSE (31/03/2004 repeated)
[30/54/2004, 31/08/2005]....FALSE (30/54/2004 not a valid date in year range)<o></o>
[dsfaf, 31/08/2005].........FALSE (dfsaf not a valid date in year range)<o></o>
[&a3!, 333, 455!E37]........FALSE (Not valid dates in year range)<o></o>
[31/03/2004, 31/08/2005]....TRUE (Dates are last day of month in year range)<o></o>
[ ].........................TRUE (Ok to have nothing in cell)<o></o>
<o></o>
Note that the square brackets in this example are not part of the cell contents<o></o>
=========================================================<o></o>
<o></o>
Thanks for your assistance.<o></o>
<o></o>
<st1:GivenName w:st="on">Michael</st1:GivenName><o></o>

 

[Peter_SSs]

Michael

I have moved to the opinion that this is not going to be solved with formulas and I'm not sure a vba approach would be very easy either. I could be wrong, but I think beyond what you could expect from a free public forum anyway.

Re: The First problem
You had said that my suggestion worked, apart from when the cell was empty or contained a 'non-number'. However, as I pointed out (post #4), my suggestion was based on the year numbers being in ascending order. Your latest samples show that this is not the case (eg [2004, 2002, 2005]).

Additionally, there was no mention previously of checking for repeated numbers (eg [2004, 2005, 2004]) and my formula certainly did not do that.

Re: The Second and Third problems
In my opinion, these are way beyond anything a formula could do.