I have a large DB with a text field that may have a fax number somewhere in
it.  I want to be able to extract into a separate field, the first 15
characters that follow a specific string...      FAX:         That could
appear *anywhere* in the notes.

Is this possible?  Is a plugin required?

I've looked at the help files and see that Patterncount seems to return a
number.  If this is possible I would like to be able to use it for other
things as well so a plugin that made this sort of thing really easy would be
preferred over more complicated calculations.

Thanks in advance

MM

<mojotogo@sbcglobal.net> wrote:

> I have a large DB with a text field that may have a fax number somewhere in
> it.  I want to be able to extract into a separate field, the first 15
> characters that follow a specific string...      FAX:         That could
> appear *anywhere* in the notes.
> 
> Is this possible?  Is a plugin required?
> 
> I've looked at the help files and see that Patterncount seems to return a
> number.  If this is possible I would like to be able to use it for other
> things as well so a plugin that made this sort of thing really easy would be
> preferred over more complicated calculations.
> 
> Thanks in advance
> 

Look at the Middle() and Position() functions, which combined will parse
out whatever you want, as long as it's a set string.


-- 
Lynn Allen              Allen & Allen Semiotics
FSA Associate           Filemaker Consulting & Training
lynn@semiotics.com      http://www.semiotics.com      

Even if it is NOT at a static position?  The FAX:  could be ANYWHERE within
the large text field.

Thanks in advance

mm


"Lynn allen" <lynn@NOT-semiotics.com> wrote in message
news:1g189mj.wdei261moms6yN@[192.168.1.101]...
> <mojotogo@sbcglobal.net> wrote:
>
> > I have a large DB with a text field that may have a fax number somewhere
in
> > it.  I want to be able to extract into a separate field, the first 15
> > characters that follow a specific string...      FAX:         That could
> > appear *anywhere* in the notes.
> >
> > Is this possible?  Is a plugin required?
> >
> > I've looked at the help files and see that Patterncount seems to return
a
> > number.  If this is possible I would like to be able to use it for other
> > things as well so a plugin that made this sort of thing really easy
would be
> > preferred over more complicated calculations.
> >
> > Thanks in advance
> >
>
> Look at the Middle() and Position() functions, which combined will parse
> out whatever you want, as long as it's a set string.
>
>
> -- 
> Lynn Allen              Allen & Allen Semiotics
> FSA Associate           Filemaker Consulting & Training
> lynn@semiotics.com      http://www.semiotics.com

mojotogo@sbcglobal.net wrote:
> Even if it is NOT at a static position?  The FAX:  could be ANYWHERE within
> the large text field.

I think this is probably what Lynn had in mind:

middle(TextField, position(TextField, "FAX:", 1, 1)+4, 15)


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California
Associate Member, FileMaker Solutions Alliance

The Position function allows you to locate the position of the tag within
the text.

The Middle function allows you to parse a specified number of characters
from a given starting point, ie from the position of the tag.

The tricky part is finding the end of the number string.

If the fax number was always a set number of numbers, you could just specify
that number.

If the fax number included numbers of different lengths but was always a
continuous string of numbers (no spaces) it wouldn't be too difficult - you
could just use the position function to locate the first or second or third
space after the tag (depending on how many spaces there are between the tag
and the number).

If the number isn't a continuous string of numbers but is always formatted
in a consistent manner, you should also be able to find the end of the
number.

But if there is no consistent formatting, it might be trickier.  In a pinch
you could use middle and position to extract an arbitrary length from the
tag (large enough to get the largest number) then use TextToNum to remove
non-numbers (this would also remove dashes and spaces and brackets, but if
these have not been applied consistently, this might be an advantage) (it
would not necessarily remove all periods since it might interpret one to be
a decimal point).

This example assumes one space between colon and fax number followed by a
string of numbers (there may be dashes or brackets but no spaces in the
number) followed by a space:

First find the text beginning with the tag "Tag: " using the right and
position functions:

Right(text, Length(text) - Position(text, "Tag: ", 1, 1) - 4)

Then use this as the text for the Middle function:

Middle(calc, 1, Position(calc, " ", 1, 1) - 1)

Finished calc:

Middle(Right(text, Length(text) - Position(text, "Tag: ", 1, 1) - 4), 1,
Position(Right(text, Length(text) - Position(text, "Tag: ", 1, 1) - 4), " ",
1, 1) - 1)

Bridget Eley



in article uY19b.3417$nQ.1224670@newssvr28.news.prodigy.com,
mojotogo@sbcglobal.net at mojotogo@sbcglobal.net wrote on 15/9/03 3:55 AM:

> Even if it is NOT at a static position?  The FAX:  could be ANYWHERE within
> the large text field.
> 
> Thanks in advance
> 
> mm
> 
> 
> "Lynn allen" <lynn@NOT-semiotics.com> wrote in message
> news:1g189mj.wdei261moms6yN@[192.168.1.101]...
>> <mojotogo@sbcglobal.net> wrote:
>> 
>>> I have a large DB with a text field that may have a fax number somewhere
> in
>>> it.  I want to be able to extract into a separate field, the first 15
>>> characters that follow a specific string...      FAX:         That could
>>> appear *anywhere* in the notes.
>>> 
>>> Is this possible?  Is a plugin required?
>>> 
>>> I've looked at the help files and see that Patterncount seems to return
> a
>>> number.  If this is possible I would like to be able to use it for other
>>> things as well so a plugin that made this sort of thing really easy
> would be
>>> preferred over more complicated calculations.
>>> 
>>> Thanks in advance
>>> 
>> 
>> Look at the Middle() and Position() functions, which combined will parse
>> out whatever you want, as long as it's a set string.
>> 
>> 
>> -- 
>> Lynn Allen              Allen & Allen Semiotics
>> FSA Associate           Filemaker Consulting & Training
>> lynn@semiotics.com      http://www.semiotics.com
> 
> 

Howard Schlossberg <howard@antispahm.fmprosolutions.com> wrote:

> mojotogo@sbcglobal.net wrote:
> > Even if it is NOT at a static position?  The FAX:  could be ANYWHERE within
> > the large text field.
> 
> I think this is probably what Lynn had in mind:
> 
> middle(TextField, position(TextField, "FAX:", 1, 1)+4, 15)

Thank you, Howard, I was too tired to calculate. ;)
-- 
Lynn Allen              Allen & Allen Semiotics
FSA Associate           Filemaker Consulting & Training
lynn@semiotics.com      http://www.semiotics.com