BoostExcel main content

Simple Sample: Splitting City State ZIP

Template#: c4072

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.
365 KBDownload

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

Screenshot

The screen shot for Simple Sample: Splitting City State ZIP

Detail

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.

Format and Specification

Template#c4072
Belongs to
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

Related

Simple Service Invoice Template Thumbnail
Simple 2-Column Sales Invoice Sample Thumbnail
Simple Sales Invoice Template without Item Description Thumbnail
Simple 2-Column Service Bill Sample Thumbnail
Simple Sample: Manually Fillable Line-Totals Thumbnail
Simple Service Bill with Pleased Customer Image Thumbnail
Simple Sales Invoicing Sample Thumbnail