Reformat Existing Phone numbers

[RjC]

I imported over 16k records from an Excel spreadsheet into Filemaker 10. In Excel, the phone numbers formated as (222) 222-2222. After import, I noticed all the phone numbers are now one long string (2222222222). Is there some way to reformat existing phone numbers? The phone field is currently set as text.

Any help would be appreciated. thanks.

[Søren Dyhr]

Investigate this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000799

--sd

[Lee Smith]

Hi RJC, and welcome to the forum.

You can set up the field with the Auto Enter calculation that is shown in the Linked File.

You will also need to deal with the existing data.

I suggest that you use a loop script to modify your field. I used the calculation as shown in John Mark Osborne's file in the following script.

Go to Layout [ “YourPhoneNoField” ]

Go to Record/Request/Page

[ First ]

Loop

Set Field [ AlphaButtons::Phone; Let(

@NumbersOnly = Filter(AlphaButtons::Phone; "0123456789");

"(" & Left(@NumbersOnly; 3) & ") " &

Middle(@NumbersOnly; 4; 3) & "-" &

Right(@NumbersOnly; 4)

) ]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

HTH

Lee

Edited July 23, 2009 by Guest