Is there some way to extract phone numbers from where ever they appear in a
text field?


Pattern count seems to return a number so I'm not sure if it could help.


SAMPLES

Yard Sale: Moving out of town. Must sell a lot of furniture & other items.
614-888-7845 Begins Sun. & Mon. at 8am. 375 W. 7th Ave. (S.E. corner of 7th
Ave. & Michigan)

Scioto Woods Moving Sale (between Wilson & Trabue). 3426 Lindstrom Dr. Sat.
May 29th, 9am - 4pm. Something for everyone!  614-889-8225  Come and get it!

MOVING SALE -- 614-891-7895  2nd weekend Everything must go!   Sat. May 29,
9a-3p. 23 East Woodfield, Pataskala. (Etna). Antique office desk, toys, many
miscellaneous items.


So that the phone number is copied into its own field...
614-888-7845
614-889-8225
614-891-7895


Thanks for any help

d

Is there always one and one phone number only in the text field?  Are the
phone numbers formatted exactly the same in every field?  Here are some
common variations:

614-888-7845
(614) 888-7845
614.888.7845
888-7845

Assuming that all phone numbers follow the exact format you listed,
614-888-7845, then you need to look at the data to see what is unique
about the phone number that will allow it to be separated out.  Is your
data local?  That is, do all phone numbers have a 614 area code?  Are
there only a few possible area codes, say, ten?  If so, you can use this
to identify the number.

If the data is national, with a great many area codes, then the only other
aspect would be the hyphen.  Can you guarantee that only the phone number
contains a hyphen, that no hyphens appear anywhere else in the text?

You can check this by setting up a temporary calculation field where the
calc is PatternCount (TextField, "-").  If all data contains phone numbers
exactly the way you described, and no hyphens appear elsewhere in the
data, then the content of this field should be "2" in all records.  You
could sort your data on this field and look at both ends of the list.  See
how many records have three or more hyphens, and see how many have less
than two.

There are a variety of ways to approach your problem.  Selecting the best
and easiest depends on the answers to these questions.

Also, please let us know which version of Filemaker you are using, and on
which platform.

Steve Brown


In article <L9-dnTeBx6mWsyfdRVn-jg@wideopenwest.com>, "mkmissy"
<mkmissy@mojotogo.net> wrote:

>Is there some way to extract phone numbers from where ever they appear in a
>text field?
>
>
>Pattern count seems to return a number so I'm not sure if it could help.
>
>
>SAMPLES
>
>Yard Sale: Moving out of town. Must sell a lot of furniture & other items.
>614-888-7845 Begins Sun. & Mon. at 8am. 375 W. 7th Ave. (S.E. corner of 7th
>Ave. & Michigan)
>
>Scioto Woods Moving Sale (between Wilson & Trabue). 3426 Lindstrom Dr. Sat.
>May 29th, 9am - 4pm. Something for everyone!  614-889-8225  Come and get it!
>
>MOVING SALE -- 614-891-7895  2nd weekend Everything must go!   Sat. May 29,
>9a-3p. 23 East Woodfield, Pataskala. (Etna). Antique office desk, toys, many
>miscellaneous items.
>
>
>So that the phone number is copied into its own field...
>614-888-7845
>614-889-8225
>614-891-7895
>
>
>Thanks for any help
>
>d

In article <L9-dnTeBx6mWsyfdRVn-jg@wideopenwest.com>, "mkmissy"
<mkmissy@mojotogo.net> wrote:

> Is there some way to extract phone numbers from where ever they appear in a
> text field?
> 
> 
> Pattern count seems to return a number so I'm not sure if it could help.
> 
> 
> SAMPLES
> 
> Yard Sale: Moving out of town. Must sell a lot of furniture & other items.
> 614-888-7845 Begins Sun. & Mon. at 8am. 375 W. 7th Ave. (S.E. corner of 7th
> Ave. & Michigan)
> 
> Scioto Woods Moving Sale (between Wilson & Trabue). 3426 Lindstrom Dr. Sat.
> May 29th, 9am - 4pm. Something for everyone!  614-889-8225  Come and get it!
> 
> MOVING SALE -- 614-891-7895  2nd weekend Everything must go!   Sat. May 29,
> 9a-3p. 23 East Woodfield, Pataskala. (Etna). Antique office desk, toys, many
> miscellaneous items.
> 
> 
> So that the phone number is copied into its own field...
> 614-888-7845
> 614-889-8225
> 614-891-7895
> 
> 
> Thanks for any help
> 
> d


If ALL phone numbers start with "614-" and are ALL 12 digits long
(including hypens), and you only want the first phone number listed if
there's more than one, then it's an easy Calculated field returning a
Text result like this:

     PhoneNumber = 
         If(Position(TextField, "614-", 1, 1) = 0,
            "** No Phone Number **",
            Middle(TextField, Position(TextField, "614-", 1, 1), 12))
 
The 'Position(TextField, "614-", 1, 1)' function of the calculation
searches the field TextField for the text "614-" starting at the first
character in TextField and finding the first occurence of "614-". this
returns the character number (of the "6") at which the Position
function found the match or zero if there was no match.

Using the If function we can test the result of the Position function:

     - if it's a zero there was no match to "614-" and therefore 
       no phone number, so make PhoneNumber equal to "** No Phone 
       Number **"

otherwise   

     - there was a match to "614-" and the Position function 
       gives you the character number where the phone number 
       starts, so we can use the 'Middle(TextField, Position
       (TextField, "614-", 1, 1), 12)' part of the calculation 
       to grab from TextField the 12 characters starting at the
       character given by Position function's match.

*BUT*,
there obviously a few flaws here:

   - this only works with phone numbers starting with "614-", 
     so phone numbers not starting with "614-" are not found, 
     eg. 615-123-4567 or 614 123 4567 or 6411234567 or 614 123 456 
     all return as "No Phone Number"

   - this only grabs the 12 characters starting at "614-", so a 
     phone number typed in differently will return incorrectly, 
     eg. 614-1234567x324 will return as 614-1234567x

   - most importantly, if there is an address or some other "614-" 
     number before the phone number, then the result will not be 
     the phone number but something else instead,
     eg.  "Scioto Woods Moving Sale (between Wilson & Trabue). 
           614-618 Lindstrom Dr. Sat. May 29th, 9am - 4pm. 
           Something for everyone! 614-889-8225 Come and get it!" 
           will return a phone number as "614-618 Lind"

Whether these are a problem or not depends on your actual data.



Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)