This is a typical candidate for regular expression searching. Although
Filemaker Pro doesn't support regular expressions natively, Dracoventions
and other have free plugins. You would then search for a regular
expression like
",\s(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s\d{1,2},\s\d\d\d\d\s-"
where the round brackets specify a group of alternatives, \s is a
whitespace character, \d is a digit, comma and hyphen are literals. (I
haven't checked the syntax in detail, but this is the idea.)
You'd have to check the particular syntax of the regular expression engine
that you are using, and if you are not familiar with regular expressions
you might have to get your mind around them first, but ultimately they are
the most efficient solution for this kind of problem. (They do all the
middle and position calculations automatically.)
Bruce Button
On Fri, 25 Jan 2013 20:15:39 +0200, Richard S. Russell
<[log in to unmask]> wrote:
>
> On 2013 Jan 25, at 9:32, Nick Adams <[log in to unmask]> wrote:
>
>> 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.
>>
>
>
> Let me start with a simplifying assumption and show how to deal with
> this rock-bottom simple case, then we'll work our way up to the >more
> complicated ones. Part A of the simplifying assumption is that there are
> only 2 commas in the text field, the one that precedes the >date and the
> one that's IN the date. Part B is that there's only one hyphen (for a
> hyphen, not an en dash or an em dash, is what appears >in your example),
> namely the one immediately after the date.
>
> What you want to do is use the Middle function to find the text string
> "Nov 10, 2006". The syntax of "Middle" is:
> Middle ( text ; start ; numberOfCharacters )
> Let's look at those parameters one at a time.
>
> • "text" is the name of the text field you will be examining. For this
> example, I'll assume it's "blather", and I'll further assume that the
> >record we're looking at has a value of "blather" equal to:
> "The UFO landed, Nov 10, 2006 - and nothing was ever the same."
> • "start" is the beginning position of the date, counting the number of
> characters from the beginning of "blather". For instance, the "T" in
> >"The" is at Position 1, the "F" in "UFO" is at Position 6, and so on.
> Ideally, we'd like to find the position occupied by the "N" in "Nov",
> which >is Position 17. (As we will see shortly, it'll actually be easier
> to look for the position immediately after the comma, Position 16, which
> is >occupied by a space in the example.)
> • "numberOfCharacters" is the length of the character string we want to
> extract from "blather", which would be 12 for "Nov 10, 2006". The
> >trick, of course, is that it won't always be 12. For example, if
> someone put in a period ("Nov. 10, 2006"), it would be 13. If someone
> used >"Sept." instead of "Sep" or "July" instead of "Jul", same deal. So
> what we need here is not a fixed number but rather a calculation of the
> >distance between the comma and the hyphen.
>
> So here's the "Middle" formula rewritten to take the above into account:
> Middle ( blather; [position right after comma]; [position of hyphen
> minus position of comma] )
> which translates to:
> Middle ( blather; 16; 30 - 16 ) = Middle ( blather; 16; 14 )
>
> So which 14 characters will we be getting? They will be these:
> " Nov 10, 2006 "
> "But," you may be thinking, "I don't want those spaces surrounding the
> date." Can't blame you for that, but there's no way to guarantee in
> >advance that those spaces will be there all the time. What if your
> original text had read:
> "The UFO landed,Nov 10, 2006- and nothing was ever the same."
> In this case, your calculation of hyphen position minus comma position
> would have yielded 12, and you'd get exactly what you wanted, >the date
> without surrounding spaces.
>
> So the way we deal with extraneous spaces is to use the Trim function,
> outside the Middle function, thus:
> Trim ( Middle ( blather; [position right after comma]; [position of
> hyphen minus position of comma] ) )
> That way, if the Middle function yields " Nov 10, 2006 ", the Trim
> function will cut it down to just "Nov 10, 2006".
>
> OK, now let's look a little more deeply at those formulas for finding
> the position. The syntax of "Position" is:
> Position ( text ; searchString ; start ; occurrence )
> Again, let's look at the parameters one at a time:
>
> • "text" is our old friend "blather"
> • "searchString" is what you're looking for, inside quotation marks. The
> very first thing we'll be looking for is a comma, so for >that purpose,
> "searchString" = ",".
> • "start" is where within "text" you want to start searching. It's
> almost always 1 (the very 1st position, the "T" in "The").
> • "occurrence" is which occurrence of the search string you want to
> discover. Under our simplifying assumption, you'll be looking for the
> >very 1st comma, so this too will be 1.
>
> So here's the "Position" formula rewritten to take the above into
> account:
> Position ( blather; ","; 1; 1 )
> Now, that finds you the position of the comma, but what you really want
> is the position of the character immediately after the comma, so >for
> that you'd specify:
> Position ( blather; ","; 1; 1 ) + 1
> I leave it to you to figure out the formula that you should plug in for
> "[position of hyphen minus position of comma]".
>
> Try this first to see what you get. If it turns out to work for some of
> your records but not all of them, we'll probably end up breaking your
> set >of records up into batches and dealing with each batch separately,
> probably depending on the number of commas that precede the one
> >immediately before the date. But maybe we'll get lucky, and it really
> will work for 100% of them the 1st time out of the gate.
>
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 7929 (20130124) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
--
Using Opera's revolutionary email client: http://www.opera.com/mail/