# script to find all the dates including 4 digit years and months as text in a text field # filename and table names are "parse" # field being read is "In" # date field "Date" will hold the last date in the text # text field "DateList" will list all dates in the text Go to Record/Request/Page [ First ] # let's run through the found set of records Loop # reset the output field from the last run Set Field [ parse::DateList; "" ] # start with this word Set Variable [ $x; Value:1 ] # get a copy of our input that we will be free to murder later Set Variable [ $in; Value:parse::In ] # how many words do we need to look at? Set Variable [ $y; Value:WordCount ( $in ) ] # we're going to loop through each word in the text Loop # word .. Set Variable [ $word; Value:MiddleWords ( $in; $x; 1) ] # could this word be the year we are looking for? If [ (GetAsNumber ( $word ) = $word) and ( Length ( $word ) = 4) and ( GetAsNumber ( $word ) > 1900 ) and ( GetAsNumber ( $word ) < 2050 ) ] # found a 4 digit year Set Variable [ $year; Value: MiddleWords ( $in; $x; 1) ] # find our year in the text Set Variable [ $endpoint; Value:Position ( $in; $year; 1; 1) + 4 ] # and excise it Set Variable [ $dateslug; Value:RightWords ( Left ( $in; $endpoint ) ; 3 ) ] # let's build a date Set Variable [ $day; Value:GetAsNumber(MiddleWords($dateslug; 2; 1)) ] Set Variable [ $month; Value:Let ( x = LeftWords($dateslug; 1 ); Case( PatternCount ( x ; "Jan" ) ; 1 ; PatternCount ( x ; "Feb" ) ; 2 ; PatternCount ( x ; "Mar" ) ; 3 ; PatternCount ( x ; "Apr" ) ; 4 ; PatternCount ( x ; "May" ) ; 5 ; PatternCount ( x ; "Jun" ) ; 6 ; PatternCount ( x ; "Jul" ) ; 7 ; PatternCount ( x ; "Aug" ) ; 8 ; PatternCount ( x ; "Sep" ) ; 9 ; PatternCount ( x ; "Oct" ) ; 10 ; PatternCount ( x ; "Nov" ) ;11 ; PatternCount ( x ; "Dec" ) ;12 ; GetAsNumber ( x ) ) ) ] Set Variable [ $year; Value:GetAsNumber(RightWords($dateslug; 1)) ] January 26, 2013 12:24:02 parse.fp7 - go -1- go # your date, sir... Set Field [ parse::Date; Date ( $month; $day; $year) ] # append that date to the end of the list Set Field [ parse::DateList; parse::DateList & GetAsText ( parse::Date ) & "¶" ] # chop off the text up to where we just read Set Variable [ $in; Value:Right( $in; Length ( $in ) - $endpoint ) ] # how many more words do we need to look at? Set Variable [ $y; Value:WordCount ( $in ) ] # since we chopped off the already read part of the text, we need to reset the word we were looking at Set Variable [ $x; Value:0 ] Else If [ IsValid ( GetAsDate ( $word ) ) ] # just in case they entered a real date, our word search for years would have missed it Set Field [ parse::Date; GetAsDate ( $word ) ] Set Field [ parse::DateList; parse::DateList & GetAsText ( parse::Date ) & "¶" ] # and again we have to reset our variables so we can move on Set Variable [ $endpoint; Value:Position ( $in; $word; 1; 1) + Length ( $word ) ] Set Variable [ $in; Value:Right( $in; Length ( $in ) - $endpoint ) ] Set Variable [ $y; Value:WordCount ( $in ) ] Set Variable [ $x; Value:0 ] End If # next word please Set Variable [ $x; Value:$x + 1 ] # are we out of words? Exit Loop If [ $x > $y ] End Loop # next record please Go to Record/Request/Page [ Next; Exit after last ] End Loop Geoff From: FileMaker Pro Discussions [mailto:[log in to unmask]] On Behalf Of Nick Adams Sent: Friday, January 25, 2013 10:33 AM To: [log in to unmask] Subject: parsing a date from a text field Would someone be so kind as to give me a hand. I am an amateur and only use FMP for my own projects. I am trying to parse the date out of a text field. The text field contains something like this: some text, Nov 10, 2006 - some more text The date is between a comma and a dash. I have been trying various combinations of text functions but cannot get anything meaningful. Nick Karystos, Greece