Simple Sample: Splitting City State ZIP

This template demonstrates how to store "City", "State", and "ZIP" in separate fields by using Excel formulas.

By default, our invoicing app stores "City, State ZIP" in a single field, just like a U.S.-style address line.

To split the "city, state zip" value into separate cells, we have to use Excel formulas. To store the extracted values, we have to use custom fields.

The template uses three custom fields: "CustomerCity", "CustomerState", and "CustomerZip". The fields are added to both the "Customer" and "InvHdr" database tables.

Our formulas assume that the text to be parsed is in the format "city, state zip". There is only one comma in the text following the "city" part; there is no space inside the zip or postal code itself.

This template is based on Simple Invoice Template: Discount Amount Field (c4051).

Let's see the steps involved in creating this template.

1. Backup both your Excel template and database file.
2. Open the template as usual.
3. Click the Design Mode button on the Excel ribbon tab.
4. Look for an empty area to place the extracted city, state, and zip code. In this example, we use the O6:P20 area. In cell O6, type in the name of this section, "City, State, and Zip".
5. Enter the following formula into cell O7:

   =TRIM(oknWhoCityStateZip)

   This formula removes the leading and trailing spaces.

6. Enter the following formula into cell O8:

   =IF(O7="","",LEFT(O7,(FIND(",",O7))-1))

   This formula looks for the comma symbol in the text and retrieves the left part as the "city name".

   Name this cell "oknCustomerCity".

7. Enter the following formula into cell O9:

   =IF(O7="","",MID(O7,FIND(",",O7)+2,FIND("*", SUBSTITUTE(O7, " ", "*", LEN(O7) - LEN(SUBSTITUTE(O8, " ", ""))))-(FIND(",",O7)+2)))'

   .

   This formula retrieves the part between the comma and space symbols. This is the state name. Name this cell "oknCustomerState".

8. Enter the following formula into cell O10:

   =IF(O7="","",RIGHT(O7, LEN(O7) - FIND("*", SUBSTITUTE(O7, " ", "*", LEN(O7) - LEN(SUBSTITUTE(O7, " ", "")))))) '

   This formula retrieves the zip code. Name this cell "oknCustomerZip".

9. Exit design mode.
10. The next step is to create three custom fields in the database for the separated city, state, and zip values. For more information, see Custom Fields.
11. Save the template.
12. Test the template.