On 18 Sep 2006 13:26:33 -0700, wrote:

Should I check the cell contents as a string and extact any text like,
f1 etc., and then check the remaining string with isdate?

Something like that maybe?

Ron Rosenfeld wrote:
On Mon, 18 Sep 2006 12:28:01 -0700, Tom Ogilvy
wrote:

I'll let Ron give you a regular expressions solution since he will whether I
answer or not.

And I was hoping you'd answer with a non-regular expression solution :-)
--ron

Well, what I would do would be to first ensure there is a date in the string.
Then I would remove the string and ensure that all of the remaining substrings
(defined as a series of alphanumeric characters delimited by something not
alphanumeric) can be described by the letter F followed by a single digit.

In the example I've posted, the digit can be 0-9. If you want to change it to
be 1-9, then change PatternF to "\bF[1-9]\b"

Since you posted in the programming section, I assumed you wanted a VBA
solution and not a worksheet function.

I did it by using Longre's free morefunc.xll add-in, which you can download and
install from http://xcell05.free.fr

You could use Microsoft VBScript Regular Expressions, but since I have the
morefunc add-in, I frequently find it simpler.

In any event, here is a VBA subroutine that will test each cell in a Selection
and return the contents of the cell, and a True/False depending on whether it
meets your criteria to the Immediate Window.

You'll have to modify it to do what you want with the information:

=======================================
Option Explicit

Sub Validate()
Dim c As Range
Dim strDate As String
Dim strF As String
Dim i As Long
Dim ValidEntry As Boolean
Dim sTemp As String

Const PatternDate As String = "\b\d+.?\d+.?\d+\b"
Const PatternW As String = "\b\w+\b" 'any delimited alphanumeric string
Const PatternF As String = "\bF\d\b" 'F followed by any digit

For Each c In Selection
strDate = Run([regex.mid], c.Text, PatternDate)
ValidEntry = IsDate(strDate) 'sets ValidEntry to True or False

If ValidEntry = True Then
strF = Replace(c.Text, strDate, "")
For i = 1 To Run([regex.count], strF, PatternW, False)
sTemp = Run([regex.mid], strF, PatternW, i, False)
If Run([regex.comp], sTemp, PatternF, False) = False Then
ValidEntry = False
End If
Next i
End If
Debug.Print c.Text & " " & ValidEntry
Next c

End Sub
===================================


--ron