I am trying to parse the contents of a specific cell to make sure that
the cell contains only a date, or the text "F1", or "f1", or both, a
date -and- "F1" or "f1".
The "F1" language can sometimes be: F1,F2,F3

I'm sorry I don't have any code to post, I haven't gotten anything to
work yet.

Thanks in advance,
RC

On 18 Sep 2006 10:27:40 -0700, wrote:

I am trying to parse the contents of a specific cell to make sure that
the cell contains only a date, or the text "F1", or "f1", or both, a
date -and- "F1" or "f1".
The "F1" language can sometimes be: F1,F2,F3

I'm sorry I don't have any code to post, I haven't gotten anything to
work yet.

Thanks in advance,
RC

What is the precise format of the date?

Is it always a text string? Or might it also be an "Excel" date.

If the cell contains both a date and the text 'F1...' does the cell contain a
formula to produce that, or merely the string "some date string F1"


--ron

Please restate what is acceptable.

are you saying F and any number so that F1 symbolizes F and any single
digit number?

or are you saying the cell could actually contain all of "F1,F2,F3"

and if so, then how does that relate the 4 conditions you stated (of which
it matches none).

If it contains a date and more, will the date always be first.

--
Regards,
Tom Ogilvy



" wrote:

I am trying to parse the contents of a specific cell to make sure that
the cell contains only a date, or the text "F1", or "f1", or both, a
date -and- "F1" or "f1".
The "F1" language can sometimes be: F1,F2,F3

I'm sorry I don't have any code to post, I haven't gotten anything to
work yet.

Thanks in advance,
RC

I plan to format the cell with a date format, which works well if the
user enters only a date, but sometimes the users may need to enter a
footnote also. The data can look like:
12/25/06 or
12-25-06 or
12/15/06 F1 or
12/15/06 F1,F2,F3,F4,F5,F6 or
12-25-2006 f1,f2

etc.


Tom Ogilvy wrote:
Please restate what is acceptable.

are you saying F and any number so that F1 symbolizes F and any single
digit number?

or are you saying the cell could actually contain all of "F1,F2,F3"

and if so, then how does that relate the 4 conditions you stated (of which
it matches none).

If it contains a date and more, will the date always be first.

--
Regards,
Tom Ogilvy



" wrote:

I am trying to parse the contents of a specific cell to make sure that
the cell contains only a date, or the text "F1", or "f1", or both, a
date -and- "F1" or "f1".
The "F1" language can sometimes be: F1,F2,F3

I'm sorry I don't have any code to post, I haven't gotten anything to
work yet.

Thanks in advance,
RC

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

--
Regards,
Tom Ogilvy


" wrote:

I plan to format the cell with a date format, which works well if the
user enters only a date, but sometimes the users may need to enter a
footnote also. The data can look like:
12/25/06 or
12-25-06 or
12/15/06 F1 or
12/15/06 F1,F2,F3,F4,F5,F6 or
12-25-2006 f1,f2

etc.


Tom Ogilvy wrote:
Please restate what is acceptable.

are you saying F and any number so that F1 symbolizes F and any single
digit number?

or are you saying the cell could actually contain all of "F1,F2,F3"

and if so, then how does that relate the 4 conditions you stated (of which
it matches none).

If it contains a date and more, will the date always be first.

--
Regards,
Tom Ogilvy



" wrote:

I am trying to parse the contents of a specific cell to make sure that
the cell contains only a date, or the text "F1", or "f1", or both, a
date -and- "F1" or "f1".
The "F1" language can sometimes be: F1,F2,F3

I'm sorry I don't have any code to post, I haven't gotten anything to
work yet.

Thanks in advance,
RC

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

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

Requires Excel 2000 or later.

Sub ABC()
Dim cell As Range, iloc As Long
Dim v As Variant, v1() As Variant
Dim s As String, dt As Date
Dim cnt As Long, i As Long
Dim lFNote As Long, ss As String
For Each cell In Selection
ss = Replace(Application.Trim(cell.Value), " ", ",")
iloc = InStr(1, ss, ",", vbTextCompare)
If iloc < 0 Then
v = Split(ss, ",")
dt = 0
cnt = 0
ReDim v1(1 To 1)
For i = LBound(v) To UBound(v)
s = Trim(v(i))
If IsDate(s) Then
dt = CDate(s)
ElseIf s Like "[Ff]#*" Then
cnt = cnt + 1
ReDim Preserve v1(1 To cnt)
v1(cnt) = CLng(Right(s, Len(s) - 1))
End If
Next
s = Format(dt, "mm/dd/yy")
For i = LBound(v1) To UBound(v1)
s = s & " " & v1(i)
Next
Debug.Print s
ElseIf IsDate(cell.Value) Then
dt = CDate(cell.Value)
Debug.Print dt
ElseIf Trim(cell.Value) Like "[Ff]#*" Then
s = Trim(cell.Value)
lFNote = CLng(Right(s, Len(s) - 1))
Debug.Print lFNote
End If
Next
End Sub

separates things out. I don't know what you want to do with the results.

Make the immediate window visible in the VBE (view=Immediate window in the
VBE) , select your cells, then run the macro.

--
Regards,
Tom Ogilvy




wrote in message
ups.com...
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

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

On Mon, 18 Sep 2006 21:49:50 -0400, Ron Rosenfeld
wrote:

Const PatternDate As String = "\b\d+.?\d+.?\d+\b"

A little more robust might be:

Const PatternDate As String = "\b(\d{1,2}[-/ ]){2}\d{2}(\d{2})?\b"

This requires the date string to look like:

Word Boundary then
One or Two digits followed by a separator [-/ ] then
One or Two digits followed by a separator [-/ ] then
Two or Four digits then
Word Boundary

I did not bother with a non-separated date structure (e.g. 12252006) because
VBA won't interpret it as a date, I don't believe.


--ron

Thank you very much for the great code it works great for what I asked,
but I screwed up when I asked my question. I have been trying to
modify your code without success.

The problem is that I forgot to mention that the valid cell contents
may contain either:
-- a date by itself or
-- one or more footnote references or
-- *both* a date and footnote reference(s).

If there is a date, it must be formatted like: yyyy-mm-dd and it must
be the first thing.
If there are footnote references they must be formatted like:
F1 or
f1 or (upper or lower case "f")
F1,F2 (multiples may go into double digits, up to 99 separate footnote
references is valid)
f1,f2,f3,f4,f5,f6 (multiples must be separated by a comma and no
space)
yyyy-mm-dd f1 (must be a space between the date and the first
footnote reference)
yyyy-mm-dd f1,f2

or just a date by itself

Again, sorry I wasn't clearer in my inital question.

Tom Ogilvy wrote:
Requires Excel 2000 or later.

Sub ABC()
Dim cell As Range, iloc As Long
Dim v As Variant, v1() As Variant
Dim s As String, dt As Date
Dim cnt As Long, i As Long
Dim lFNote As Long, ss As String
For Each cell In Selection
ss = Replace(Application.Trim(cell.Value), " ", ",")
iloc = InStr(1, ss, ",", vbTextCompare)
If iloc < 0 Then
v = Split(ss, ",")
dt = 0
cnt = 0
ReDim v1(1 To 1)
For i = LBound(v) To UBound(v)
s = Trim(v(i))
If IsDate(s) Then
dt = CDate(s)
ElseIf s Like "[Ff]#*" Then
cnt = cnt + 1
ReDim Preserve v1(1 To cnt)
v1(cnt) = CLng(Right(s, Len(s) - 1))
End If
Next
s = Format(dt, "mm/dd/yy")
For i = LBound(v1) To UBound(v1)
s = s & " " & v1(i)
Next
Debug.Print s
ElseIf IsDate(cell.Value) Then
dt = CDate(cell.Value)
Debug.Print dt
ElseIf Trim(cell.Value) Like "[Ff]#*" Then
s = Trim(cell.Value)
lFNote = CLng(Right(s, Len(s) - 1))
Debug.Print lFNote
End If
Next
End Sub

separates things out. I don't know what you want to do with the results.

Make the immediate window visible in the VBE (view=Immediate window in the
VBE) , select your cells, then run the macro.

--
Regards,
Tom Ogilvy




wrote in message
ups.com...
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

One more thing I forgot to add. I will email this worksheet to people
who won't have Longre's free morefunc.xll add-in

wrote:
Thank you very much for the great code it works great for what I asked,
but I screwed up when I asked my question. I have been trying to
modify your code without success.

The problem is that I forgot to mention that the valid cell contents
may contain either:
-- a date by itself or
-- one or more footnote references or
-- *both* a date and footnote reference(s).

If there is a date, it must be formatted like: yyyy-mm-dd and it must
be the first thing.
If there are footnote references they must be formatted like:
F1 or
f1 or (upper or lower case "f")
F1,F2 (multiples may go into double digits, up to 99 separate footnote
references is valid)
f1,f2,f3,f4,f5,f6 (multiples must be separated by a comma and no
space)
yyyy-mm-dd f1 (must be a space between the date and the first
footnote reference)
yyyy-mm-dd f1,f2

or just a date by itself

Again, sorry I wasn't clearer in my inital question.

Tom Ogilvy wrote:
Requires Excel 2000 or later.

Sub ABC()
Dim cell As Range, iloc As Long
Dim v As Variant, v1() As Variant
Dim s As String, dt As Date
Dim cnt As Long, i As Long
Dim lFNote As Long, ss As String
For Each cell In Selection
ss = Replace(Application.Trim(cell.Value), " ", ",")
iloc = InStr(1, ss, ",", vbTextCompare)
If iloc < 0 Then
v = Split(ss, ",")
dt = 0
cnt = 0
ReDim v1(1 To 1)
For i = LBound(v) To UBound(v)
s = Trim(v(i))
If IsDate(s) Then
dt = CDate(s)
ElseIf s Like "[Ff]#*" Then
cnt = cnt + 1
ReDim Preserve v1(1 To cnt)
v1(cnt) = CLng(Right(s, Len(s) - 1))
End If
Next
s = Format(dt, "mm/dd/yy")
For i = LBound(v1) To UBound(v1)
s = s & " " & v1(i)
Next
Debug.Print s
ElseIf IsDate(cell.Value) Then
dt = CDate(cell.Value)
Debug.Print dt
ElseIf Trim(cell.Value) Like "[Ff]#*" Then
s = Trim(cell.Value)
lFNote = CLng(Right(s, Len(s) - 1))
Debug.Print lFNote
End If
Next
End Sub

separates things out. I don't know what you want to do with the results.

Make the immediate window visible in the VBE (view=Immediate window in the
VBE) , select your cells, then run the macro.

--
Regards,
Tom Ogilvy




wrote in message
ups.com...
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

On 22 Sep 2006 14:01:37 -0700, wrote:

One more thing I forgot to add. I will email this worksheet to people
who won't have Longre's free morefunc.xll add-in

That in itself is not an issue. With the morefunc add-in comes a Menu option
to embed it within the worksheet. This enables the worksheet to be emailed
with the add-in installed and functioning.

A small modification to my previous routine will check for a valid entry given
your new rules. The TRUE or FALSE is printed in the immediate window as I'm
not sure what else you want to do with the results:

===============================
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 = "^\d{4}-\d{2}-\d{2}\b"
Const PatternF As String = "(" & PatternDate & _
")?" & "((^|\s)(F\d{1,2}(,|$))*)?"
'Not good form to make both parts of the regex _
optional, but it'll work because of other code below

For Each c In Selection
strDate = Run([regex.mid], c.Text, PatternDate)
ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

If ValidEntry = True Then
strF = Run([regex.mid], c.Text, PatternF, , False)
ValidEntry = (strF = c.Text)
End If
Debug.Print c.Text & " " & ValidEntry
Next c

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



--ron

Here is an example where the data is split into adjacent columns only if it is
valid. If invalid, a message is placed in the adjacent column:

======================================
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 = "^\d{4}-\d{2}-\d{2}\b"
Const PatternF As String = "(" & PatternDate & _
")?" & "((^|\s)(F\d{1,2}(,|$))*)?"
'Not good form to make both parts of the regex _
optional, but it'll work because of other code below

For Each c In Selection
strDate = Run([regex.mid], c.Text, PatternDate)
ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

If ValidEntry = True Then
strF = Run([regex.mid], c.Text, PatternF, , False)
ValidEntry = (strF = c.Text)
End If
Debug.Print c.Text & " " & ValidEntry

If ValidEntry = True Then
If strDate < "" Then
c.Offset(0, 1).Value = strDate
c.Offset(0, 1).NumberFormat = "yyyy-mm-dd"
End If

For i = 1 To Run([regex.count], strF, "F\d+", False)
c.Offset(0, i + 1).Value = Run([regex.mid], strF, "F\d+", i, False)
Next i
Else
c.Offset(0, 1).Value = "Invalid Entry"
End If
Next c

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

--ron

On Fri, 22 Sep 2006 22:02:34 -0400, Ron Rosenfeld
wrote:

Here is an example where the data is split into adjacent columns only if it is
valid. If invalid, a message is placed in the adjacent column:

======================================
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 = "^\d{4}-\d{2}-\d{2}\b"
Const PatternF As String = "(" & PatternDate & _
")?" & "((^|\s)(F\d{1,2}(,|$))*)?"
'Not good form to make both parts of the regex _
optional, but it'll work because of other code below

For Each c In Selection
strDate = Run([regex.mid], c.Text, PatternDate)
ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

If ValidEntry = True Then
strF = Run([regex.mid], c.Text, PatternF, , False)
ValidEntry = (strF = c.Text)
End If
Debug.Print c.Text & " " & ValidEntry

If ValidEntry = True Then
If strDate < "" Then
c.Offset(0, 1).Value = strDate
c.Offset(0, 1).NumberFormat = "yyyy-mm-dd"
End If

For i = 1 To Run([regex.count], strF, "F\d+", False)
c.Offset(0, i + 1).Value = Run([regex.mid], strF, "F\d+", i, False)
Next i
Else
c.Offset(0, 1).Value = "Invalid Entry"
End If
Next c

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

--ron


By the way, if you really don't want to use morefunc.xll, you can try this
variation, which might work. It, hopefully, will set a reference to Microsoft
VBScript Regular Expressions 5.5 and use some custom functions.

I wrote it as an exercise, to try to learn about setting references
programmatically.

==================================
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

'set reference to Regular Expression Library
Const s As String = "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}"
Dim RegExpInstalled As Boolean
RegExpInstalled = False
With ThisWorkbook.VBProject.References
For i = 1 To .Count
If .Item(i).GUID = s Then RegExpInstalled = True
Next i
If RegExpInstalled = False Then
.AddFromGuid s, 0, 0
End If
End With

Const PatternDate As String = "^\d{4}-\d{2}-\d{2}\b"
Const PatternF As String = "(" & PatternDate & _
")?" & "((^|\s)(F\d{1,2}(,|$))*)?"
'Not good form to make both parts of the regex _
optional, but it'll work because of other code below

For Each c In Selection
strDate = REMid(c.Text, PatternDate)
ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

If ValidEntry = True Then
strF = REMid(c.Text, PatternF, , False)
ValidEntry = (strF = c.Text)
End If
Debug.Print c.Text & " " & ValidEntry

If ValidEntry = True Then
If strDate < "" Then
c.Offset(0, 1).Value = strDate
c.Offset(0, 1).NumberFormat = "yyyy-mm-dd"
End If

For i = 1 To RECount(strF, "F\d+", False)
c.Offset(0, i + 1).Value = REMid(strF, "F\d+", i, False)
Next i
Else
c.Offset(0, 1).Value = "Invalid Entry"
End If
Next c
End Sub

Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True) _
As Variant 'Variant as value may be string or array

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Dim i As Long 'counter
Dim t() As String 'container for array results

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function

Function RECount(str As String, Pattern As String, _
Optional CaseSensitive As Boolean = True) As Long

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.
RECount = colMatches.Count
Else
RECount = 0
End If
End Function
==============================
--ron

Thank you so much for the code. With the last code, the one the sets a
reference, I get the following error message: "Programmatic access to
Visual Basic Project is not trusted", probably because I am working on
a corporate pc that as restricted access rights. Regarding the
morefunc add-in where do I find the Menu option
to embed it within the worksheet, is this something I have to set?

With the other code, that has this line:

ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

the problem is that dates are not recognized when they are accompanied
by other text. Maybe the solution is to require the user to put the
date (or no date) in one cell and any (if any) accompanying footnote
references in the following cell, then for my purposes of having the
data together, I can validate the separate cells first, then combine
the contents of both cells into a hidden cell. If would be a whole lot
easier to validate the cell contents if the cell didn't have both
dates, and text. Especially when the dates are formatted randomly
because the built in cell formatting for dates won't work if the cell
contains both a date and text.

Ron Rosenfeld wrote:
On Fri, 22 Sep 2006 22:02:34 -0400, Ron Rosenfeld
wrote:

Here is an example where the data is split into adjacent columns only if it is
valid. If invalid, a message is placed in the adjacent column:

======================================
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 = "^\d{4}-\d{2}-\d{2}\b"
Const PatternF As String = "(" & PatternDate & _
")?" & "((^|\s)(F\d{1,2}(,|$))*)?"
'Not good form to make both parts of the regex _
optional, but it'll work because of other code below

For Each c In Selection
strDate = Run([regex.mid], c.Text, PatternDate)
ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

If ValidEntry = True Then
strF = Run([regex.mid], c.Text, PatternF, , False)
ValidEntry = (strF = c.Text)
End If
Debug.Print c.Text & " " & ValidEntry

If ValidEntry = True Then
If strDate < "" Then
c.Offset(0, 1).Value = strDate
c.Offset(0, 1).NumberFormat = "yyyy-mm-dd"
End If

For i = 1 To Run([regex.count], strF, "F\d+", False)
c.Offset(0, i + 1).Value = Run([regex.mid], strF, "F\d+", i, False)
Next i
Else
c.Offset(0, 1).Value = "Invalid Entry"
End If
Next c

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

--ron


By the way, if you really don't want to use morefunc.xll, you can try this
variation, which might work. It, hopefully, will set a reference to Microsoft
VBScript Regular Expressions 5.5 and use some custom functions.

I wrote it as an exercise, to try to learn about setting references
programmatically.

==================================
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

'set reference to Regular Expression Library
Const s As String = "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}"
Dim RegExpInstalled As Boolean
RegExpInstalled = False
With ThisWorkbook.VBProject.References
For i = 1 To .Count
If .Item(i).GUID = s Then RegExpInstalled = True
Next i
If RegExpInstalled = False Then
.AddFromGuid s, 0, 0
End If
End With

Const PatternDate As String = "^\d{4}-\d{2}-\d{2}\b"
Const PatternF As String = "(" & PatternDate & _
")?" & "((^|\s)(F\d{1,2}(,|$))*)?"
'Not good form to make both parts of the regex _
optional, but it'll work because of other code below

For Each c In Selection
strDate = REMid(c.Text, PatternDate)
ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

If ValidEntry = True Then
strF = REMid(c.Text, PatternF, , False)
ValidEntry = (strF = c.Text)
End If
Debug.Print c.Text & " " & ValidEntry

If ValidEntry = True Then
If strDate < "" Then
c.Offset(0, 1).Value = strDate
c.Offset(0, 1).NumberFormat = "yyyy-mm-dd"
End If

For i = 1 To RECount(strF, "F\d+", False)
c.Offset(0, i + 1).Value = REMid(strF, "F\d+", i, False)
Next i
Else
c.Offset(0, 1).Value = "Invalid Entry"
End If
Next c
End Sub

Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True) _
As Variant 'Variant as value may be string or array

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Dim i As Long 'counter
Dim t() As String 'container for array results

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function

Function RECount(str As String, Pattern As String, _
Optional CaseSensitive As Boolean = True) As Long

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.
RECount = colMatches.Count
Else
RECount = 0
End If
End Function
==============================
--ron

On 25 Sep 2006 08:33:53 -0700, wrote:

Thank you so much for the code. With the last code, the one the sets a
reference, I get the following error message: "Programmatic access to
Visual Basic Project is not trusted", probably because I am working on
a corporate pc that as restricted access rights.

In Excel, go to Tools/Options/Security and select Macro Security. Then choose
either medium or low for developmental work.

At some time, you may wish to have your IT personnel set this up as a signed
macro.


Regarding the
morefunc add-in where do I find the Menu option
to embed it within the worksheet, is this something I have to set?

At the time of installation, you should get an option to install this menu
item. After installed it will appear at Tools/Morefunc.


With the other code, that has this line:

ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

the problem is that dates are not recognized when they are accompanied
by other text.

Why should that be a problem in that line? A ValidEntry either

1. Consists solely of a date
2. Starts with an "f" or "F
3. Or starts with a valid date and is followed by text.

Since strDate consists of only the date portion of the string, or would be null
if there is no date, the contents of the rest of the string is irrelevant for
this initial test.

The remainder of the string, if present, gets tested further down.
--ron

I figured out how to get the code to work that sets a reference. No
matter which code I run the line: strDate = REMid(c.Text, PatternDate)
strDate comes up empty, when I hover over it I get empty double quotes,
even when the only content of the cell is 1/1/2006


Ron Rosenfeld wrote:
On 25 Sep 2006 08:33:53 -0700, wrote:

Thank you so much for the code. With the last code, the one the sets a
reference, I get the following error message: "Programmatic access to
Visual Basic Project is not trusted", probably because I am working on
a corporate pc that as restricted access rights.

In Excel, go to Tools/Options/Security and select Macro Security. Then choose
either medium or low for developmental work.

At some time, you may wish to have your IT personnel set this up as a signed
macro.


Regarding the
morefunc add-in where do I find the Menu option
to embed it within the worksheet, is this something I have to set?

At the time of installation, you should get an option to install this menu
item. After installed it will appear at Tools/Morefunc.


With the other code, that has this line:

ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

the problem is that dates are not recognized when they are accompanied
by other text.

Why should that be a problem in that line? A ValidEntry either

1. Consists solely of a date
2. Starts with an "f" or "F
3. Or starts with a valid date and is followed by text.

Since strDate consists of only the date portion of the string, or would be null
if there is no date, the contents of the rest of the string is irrelevant for
this initial test.

The remainder of the string, if present, gets tested further down.
--ron

On 26 Sep 2006 09:37:37 -0700, wrote:

I figured out how to get the code to work that sets a reference. No
matter which code I run the line: strDate = REMid(c.Text, PatternDate)
strDate comes up empty, when I hover over it I get empty double quotes,
even when the only content of the cell is 1/1/2006



That is in accord with your specification. You wrote:

"If there is a date, it must be formatted like: yyyy-mm-dd and it must
be the first thing."

1/1/2006 is m/d/yyyy (or d/m/yyyy) but it clearly is NOT yyyy-mm-dd


--ron

Once again, I messed up with my question/request. I am really sorry
that I am having such a dificult time being clear about things. The
user will fill in the dates any way they want. If the cell contains
only a date, then I can set the cell format to yyyy-mm-dd and then
whatever the user types in, Excel will re-format it and then when I
extract the date I will get the yyyy-mm-dd format. But, when other
text is in the cell with the date, the formatting doesn't work so then
I have to deal with a variety of date formats. Also, then I have to
re-format the dates through vba after validating the cell contents.
Maybe, I could first look for something that looks sort of like a date
and then pop up a message telling the user to format it like yyyy-mm-dd
if it does not match that format, then do the validation.

Ron Rosenfeld wrote:
On 26 Sep 2006 09:37:37 -0700, wrote:

I figured out how to get the code to work that sets a reference. No
matter which code I run the line: strDate = REMid(c.Text, PatternDate)
strDate comes up empty, when I hover over it I get empty double quotes,
even when the only content of the cell is 1/1/2006



That is in accord with your specification. You wrote:

"If there is a date, it must be formatted like: yyyy-mm-dd and it must
be the first thing."

1/1/2006 is m/d/yyyy (or d/m/yyyy) but it clearly is NOT yyyy-mm-dd


--ron

On 26 Sep 2006 10:49:41 -0700, wrote:

Once again, I messed up with my question/request. I am really sorry
that I am having such a dificult time being clear about things. The
user will fill in the dates any way they want. If the cell contains
only a date, then I can set the cell format to yyyy-mm-dd and then
whatever the user types in, Excel will re-format it and then when I
extract the date I will get the yyyy-mm-dd format. But, when other
text is in the cell with the date, the formatting doesn't work so then
I have to deal with a variety of date formats. Also, then I have to
re-format the dates through vba after validating the cell contents.
Maybe, I could first look for something that looks sort of like a date
and then pop up a message telling the user to format it like yyyy-mm-dd
if it does not match that format, then do the validation.


That changes the algorithm.

What we will do is first look at the first word in the string.

If the "word" is recognizable as a date by VBA; or if the word starts with an
"F", then we have a chance of the entry being valid and can do the rest of the
testing.

In addition, if the first word is recognizable as a date, we will reformat it
according to your specifications.

For now, and common to using Excel, we will require that the date includes
separators (i.e. 20061225 would not be valid). If this is an issue we can
address it subsequently.

Also, in your specifications, you also wrote:

"f1,f2,f3,f4,f5,f6 (multiples must be separated by a comma and no
space)"

Is this a requirement for data input, or not?

Finally, once you have checked the data for validity, what do you want to do
with the data?

If you are going to split it out into separate cells, then what is the purpose
of the "no space" criterion?

Do you, perhaps, want it all in one cell formatted as specified?
--ron

Ron Rosenfeld wrote:
On 26 Sep 2006 10:49:41 -0700, wrote:

Once again, I messed up with my question/request. I am really sorry
that I am having such a dificult time being clear about things. The
user will fill in the dates any way they want. If the cell contains
only a date, then I can set the cell format to yyyy-mm-dd and then
whatever the user types in, Excel will re-format it and then when I
extract the date I will get the yyyy-mm-dd format. But, when other
text is in the cell with the date, the formatting doesn't work so then
I have to deal with a variety of date formats. Also, then I have to
re-format the dates through vba after validating the cell contents.
Maybe, I could first look for something that looks sort of like a date
and then pop up a message telling the user to format it like yyyy-mm-dd
if it does not match that format, then do the validation.


That changes the algorithm.

What we will do is first look at the first word in the string.

If the "word" is recognizable as a date by VBA; or if the word starts with an
"F", then we have a chance of the entry being valid and can do the rest of the
testing.

In addition, if the first word is recognizable as a date, we will reformat it
according to your specifications.

For now, and common to using Excel, we will require that the date includes
separators (i.e. 20061225 would not be valid). If this is an issue we can
address it subsequently.

Also, in your specifications, you also wrote:

"f1,f2,f3,f4,f5,f6 (multiples must be separated by a comma and no
space)"

Is this a requirement for data input, or not?

Yes, this is a requirement for data input, not my requirement, the
recipient wants it that way. If there is a footnote reference after a
date, there must be a space separating the date and the first footnote
reference.

Finally, once you have checked the data for validity, what do you want to do
with the data?

If you are going to split it out into separate cells, then what is the purpose
of the "no space" criterion?

Do you, perhaps, want it all in one cell formatted as specified?

All in one cell formatted as specified.
yyyy-mm-dd or
F1 (upper/lower case irrelevant) or
yyyy-mm-dd F1 or
yyyy-mm-dd F1,f2,f3 (F casing irrelevant)

Thanks again, I'm really stuck on how to check the date when it is
formatted in many different ways and mixed with text, or how to split
it from the footnote references to check it seperately.

--ron

On 27 Sep 2006 11:19:13 -0700, wrote:


Ron Rosenfeld wrote:
On 26 Sep 2006 10:49:41 -0700, wrote:

Once again, I messed up with my question/request. I am really sorry
that I am having such a dificult time being clear about things. The
user will fill in the dates any way they want. If the cell contains
only a date, then I can set the cell format to yyyy-mm-dd and then
whatever the user types in, Excel will re-format it and then when I
extract the date I will get the yyyy-mm-dd format. But, when other
text is in the cell with the date, the formatting doesn't work so then
I have to deal with a variety of date formats. Also, then I have to
re-format the dates through vba after validating the cell contents.
Maybe, I could first look for something that looks sort of like a date
and then pop up a message telling the user to format it like yyyy-mm-dd
if it does not match that format, then do the validation.


That changes the algorithm.

What we will do is first look at the first word in the string.

If the "word" is recognizable as a date by VBA; or if the word starts with an
"F", then we have a chance of the entry being valid and can do the rest of the
testing.

In addition, if the first word is recognizable as a date, we will reformat it
according to your specifications.

For now, and common to using Excel, we will require that the date includes
separators (i.e. 20061225 would not be valid). If this is an issue we can
address it subsequently.

Also, in your specifications, you also wrote:

"f1,f2,f3,f4,f5,f6 (multiples must be separated by a comma and no
space)"

Is this a requirement for data input, or not?

Yes, this is a requirement for data input, not my requirement, the
recipient wants it that way. If there is a footnote reference after a
date, there must be a space separating the date and the first footnote
reference.

Finally, once you have checked the data for validity, what do you want to do
with the data?

If you are going to split it out into separate cells, then what is the purpose
of the "no space" criterion?

Do you, perhaps, want it all in one cell formatted as specified?

All in one cell formatted as specified.
yyyy-mm-dd or
F1 (upper/lower case irrelevant) or
yyyy-mm-dd F1 or
yyyy-mm-dd F1,f2,f3 (F casing irrelevant)

Thanks again, I'm really stuck on how to check the date when it is
formatted in many different ways and mixed with text, or how to split
it from the footnote references to check it seperately.

--ron

OK. Try this.

The following will allow data input as follows:

The Date can be in any format recognizable by VBA. As far as I can tell, that
means that the entry must have some separator between the date portions. What
that means is that something like 20061225 would be invalid.

In addition, the footnotes must look like "F" (or "f") followed by 1 or 2
digits.

The output will be written in a cell in the adjacent column, in the format you
have specified.

This routine does NOT check for the absence of a <space after the footnote,
but it eliminates any spaces in the output.

Examples (view with a fixed pitch font):

INPUT OUTPUT
12/25/2006 2006-12-06
12/25/2006 G6 Invalid Entry
12/15/06 F1 2006-12-06 F1
12/15/06 F1,F2,F3,F4,F5,F6 2006-12-06 F1,F2,F3,F4,F5,F6
12-25-2006 f1, f11 2006-12-06 f1,f11
F1, f10, f99 F1,f10,f99


================================================== ======
Option Explicit
Sub Validate()
Dim c As Range
Dim ValidEntry As Boolean
Dim sDate As String
Dim i As Long
Dim sTemp As String
Dim sRes As String

'set reference to Regular Expression Library
Const s As String = "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}"
Dim RegExpInstalled As Boolean
RegExpInstalled = False
With ThisWorkbook.VBProject.References
For i = 1 To .Count
If .Item(i).GUID = s Then RegExpInstalled = True
Next i
If RegExpInstalled = False Then
.AddFromGuid s, 0, 0
End If
End With

Const pFirstWord As String = "^\S+(\s|$)"
Const pFnum As String = "\b[Ff][1-9]\d?\b"


For Each c In Selection

'is first word a date?

sDate = REMid(c.Text, pFirstWord)
If IsDate(sDate) Then
sDate = Format(CDate(sDate), "yyyy-mm-yy")
sTemp = Replace(c.Text, REMid(c.Text, pFirstWord), "")
Else
sDate = ""
sTemp = c.Text
End If

sRes = sDate & " " 'Result starts with formatted date if present

'check that each word is a valid Fnum
For i = 1 To RECount(sTemp, "\w+")
If REMid(sTemp, "\w+", i) = REMid(sTemp, pFnum, i) Then
sRes = sRes & REMid(sTemp, pFnum, i) & ","
Else
sRes = "Invalid Entry "
Exit For
End If
Next i
sRes = Trim(Left(sRes, Len(sRes) - 1))
Debug.Print c.Text & " converts to: " & sRes
c.Offset(0, 1).Value = sRes
Next c

End Sub

Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True) _
As Variant 'Variant as value may be string or array

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Dim i As Long 'counter
Dim t() As String 'container for array results

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function

Function RECount(str As String, Pattern As String, _
Optional CaseSensitive As Boolean = True) As Long

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.
RECount = colMatches.Count
Else
RECount = 0
End If
End Function
==================================
--ron