Simple Sample: Separate City State ZIP

This tutorial shows how to store "City, State ZIP" in three separate custom fields.

The form at Splitting City State ZIP (c4072) demonstrates how to use Excel formulas to split a field value of user-entered "City, State ZIP" into three fields and then store them in three database fields. The drawback of that solution is that it relies on the format of the user-entered values. For example, a United States-style format like "La Jolla, California 92092-0100".

A more stable solution is to let the end-user enter these three values, "City", "State", and Zip," into three different cells so that it will no longer rely on Excel formulas to do the separation.

To make the text shown on the printed invoice match exactly the specification, i.e., the "City" name followed by a comma and the "State" name followed by a space, we'll put the editable "city", "state", and "zip" fields outside the printable range and use an Excel formula to concatenate what the user entered.

This tutorial uses Splitting City State ZIP (c4072) as the base template.

1. Backup the template.
2. Open it.
3. Click the "Design Mode" button on the ribbon.
4. Drag your mouse to select the three custom fields, "oknCustomerCity", "oknCustomerState", and "oknCustomerZip".
5. Hit the DEL key on the keyboard to delete the formulas in these three cells.
6. Click and select the "City, State ZIP" cell in the "Bill To" section.
7. Enter this formula for the cell:

   = CONCATENATE( TRIM ( oknCustomerCity ) , ", ",  TRIM( oknCustomerState ),  " ", TRIM ( oknCustomerZIP ))

   This formula combines the three user-entered values into one readable and formal "City, State ZIP" value.

8. Exit design mode.
9. Save the template.