Home |
Search |
Today's Posts |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
parse cell contents
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help - need to parse a cell | Excel Worksheet Functions | |||
Parse cell contents to new columns | Excel Worksheet Functions | |||
Parse cell value based on contents | Excel Discussion (Misc queries) | |||
Parse cell contents ? | Excel Discussion (Misc queries) | |||
Parse contents of cell | Excel Discussion (Misc queries) |