AnsweredAssumed Answered

How to Separate Name and Address into Separate Fields

Question asked by ArchitectureConceptsLighting on Nov 20, 2012
Latest reply on Nov 29, 2012 by pfroelicher

My intent is to copy from a company's website to copy to my clipboard their full address info from from their contact page with exception of Company name email, phone , etc unless that can be done as well. Pa





If your database has name and address entered into a single field, you can break out this information into separate fields. The name and address may be in a single field because you originally entered the data that way, or because you imported names and addresses from another program or database.

In FileMaker, you separate the name and address information into separate fields by defining calculation fields. Here’s one example. These calculation fields make the following assumptions (you may have to modify these calculation formulas to fit your situation):

  1. The name includes only a first and last name (no middle initial or title)
  2. There is a comma between the city and state
  3. The state is two characters
  4. The zip code is 5 characters
  5. The address is one line long
  6. The field with the name and address is called Full Text, and is three lines long: a return is typed between the name and address, another return is typed between address and city

Field Name - Field Type
FullText - Text
FirstName - Calculation (Text)

Formula: Left (FullText,Position (FullText," ",1) -1)
LastName - Calculation (Text)
Formula: Middle (FullText,Position (FullText," ",1) + 1,Position (FullText,"¶",1) - Position (FullText," ",1) - 1)
Address - Calculation (Text)
Formula: Middle (FullText,Position (FullText,"¶",1) +1,Position (FullText,"¶",Position (FullText,"¶",1) + 1) - Position (FullText,"¶",1) - 1)
CityStateZip - Calculation (Text)
Formula: Right (FullText,Length (FullText) - Position (FullText,"¶",Position (FullText,"¶",1) + 1))
City - Calculation (Text)
Formula: Left (CityStateZip,Position (CityStateZip,",",1) -1)
State - Calculation (Text)
Formula: Middle (CityStateZip,Position (CityStateZip,", ",1) + 2, 2)
Zip - Calculation (Text)
Formula: Right (CityStateZip,5)

After the calculation fields have calculated, verify that they are working properly in Browse. You can then go back to Define Fields and change them from calculation fields to text fields. This will freeze the name and address values in those fields. You can then delete the original Full Text field and enter new names and addresses into the separate fields.

Note: The CityStateZip field does not need to appear on any layouts. It is created as an intermediate field to creating the separate City, State and Zip fields. If you change these calculation fields to text fields, you can delete the CityStateZip field along with the FullText field.