Download

DescriptionSizeDownload

The template works with both the Microsoft Store edition and the desktop edition of the app. Make sure you have installed one of them.

How to use:

  1. Download the EXE package.
  2. Double-click to extract the files into a folder. Make sure you have full access privileges to the folder, such as a subfolder inside “My Documents”.
  3. Open the template.
466 KBDownload

License: Private Use (not for distribution or resale). See our Terms of Use.

Screenshot

screenshot for Simple Sample: Separate City State ZIP

Detail

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.

Format and Specification

Template#c4081
Belongs to

Sales

Format (XLS or XLSX).xlsx
Columns4
Lines12
Line Height (Points)18.00
Print Area$D$2:$K$47
Papaer OrientationPortrait
Default Margins (Points)
Left22.68
Right22.68
Top45.35
Bottom45.35
PriceFree