Skip to main content
InvoicingTemplate.com main content
Sharing Is Caring! We do appreciate your efforts to let others know us!

Screenshots and Images

The printed version.

Simple Sample - Splitting City State ZIP

Simple Sample - Splitting City State ZIP (c4072)

c4072 now includes the customized invoice template for the latest version of Uniform Invoice Software. This is what you will see when you open simple-invoice-template-us-splitting-city-state-zip-uis.xlsx.

Simple Sample - Splitting City State ZIP (UIS Edition)

Simple Sample - Splitting City State ZIP - UIS edition

Download

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

You are safe to download the resources. We've added our digital signatures to the files.

DescriptionSizeDownload

Without database file. How to download and use

74 KBDownload

Download this if you have already installed UIS, but are looking for additional customized templates.

299 KBDownload

Download this if you want this design to be set as the default template by the installer program.

2.79 MBDownload

Detail

On our templates "City, State ZIP" are usually put into one single cell, like a U.S. style address line (There is an option for UK style postal code on a separate cell). This simple invoice template shows you how to extract and store separate city, state and zip.

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

In this case, assuming both the customer data and invoice data need separate city, state and zip code (postal code), we need to add the custom fields (CustomerCity, CustomerState, CustomerZip) into both the "Customer" and "Invoice Header" database tables.

Another assumption is that the text to be parsed should be in the format "city, state zip" - i.e. there is only one comma in the text following the "city" part; there is no space inside the zip or postal code itself.

With these two assumptions made, now let's see how to customize the basic invoice template, Simple Invoice Template - Discount Amount Field (also see the gallery of simple invoicing template), to split the "city, state zip" into separate fields.

  1. Backup both your Excel Invoice Template and database file.
  2. Open the invoice template as usual.
  3. Click the Design Mode button on Excel "Invoice" ribbon tab. Note that to show the "Invoice" ribbon tab you have to install Uniform Invoice Software, our Excel invoicing software that leverage both the features of front-end Excel and back-end database.
  4. Look for an empty area to place the extracted city, state and zip values. Since we'll not show the extracted values on the printed invoices or auto-generated PDF invoices. In this case we choose the O6:P20 area outside of the printable invoice form. In cell o6, type in the name of this section - "separate U.S style city state zip".
  5. Click to select cell O7. Type in the formula ' =TRIM(oknWhoCityStateZip)'. This formula makes corrections to the original value - i.e. removing the leading and trailing spaces.
  6. Click to select cell O8. Type in the Excel formula ' =IF(O7="","",LEFT(O7,(FIND(",",O7))-1))'. This formula looks for the common symbol in the text, and retrieve the left part as the "city name". Click to activate the name box on the left-side of the formula bar, enter the name of this cell "oknCustomerCity".
  7. Click to select cell O9. Type in the Excel formula ' =IF(O7="","",MID(O7,FIND(",",O7)+2,FIND("*", SUBSTITUTE(O7, " ", "*", LEN(O7) - LEN(SUBSTITUTE(O8, " ", ""))))-(FIND(",",O7)+2)))'. This formula retrieve the part between the comma and space symbols. This is the state name. Name this cell "oknCustomerState".
  8. Click to select cell O10. Type in the formula ' =IF(O7="","",RIGHT(O7, LEN(O7) - FIND("*", SUBSTITUTE(O7, " ", "*", LEN(O7) - LEN(SUBSTITUTE(O7, " ", "")))))) '. This formula retrieve the zip / postal code from the "city, state zip string. Name this cell "oknCustomerZip".

    With all these formulas correctly set, if you enter "La Jolla, California 92092-0100" into the "oknWhoCityStateZip" cell, the "oknCustomerCity" cell should show "La Jolla", the "oknCustomerStateCell" should show "California", and the "oknCustomerZip" cell should show "92092-0100".

  9. Now exit design mode by clicking the "Design Mode" button again on the "Invoice" ribbon tab.
  10. Click the "Customers" button on the "Invoice" ribbon.
  11. Click "Custom Fields", and then "Add Field".
  12. Enter the name of the field "CustomerCity", choose its type "Text", and set the size to "50". Click OK to create the custom field.
  13. Click "Add Field" again. Create the second custom field - Name: oknCustomerState; Type: Text; Size: 50. Click OK to create the custom field.
  14. Click "Add Field" again. Create the third custom field - Name: oknCustomerZip; Type: Text; Size: 50. Click OK to create the custom field.
  15. Now from the "Database table" list, choose "Invoice Header". Click "Add Field".
  16. Repeat the above steps to add "CustomerCity", "CustomerState" and "CustomerZip" custom fields for the Invoice Header database table.
  17. Click "Close" to close the custom field manager.
  18. Save the invoice template by clicking the Save button on Excel quick access toolbar.
  19. Test the template.

One of the advantages of having city, state and zip in one single cell is that it is easy to manually enter the information, and the printed result (or the auto-generated PDF invoice) looks exactly as what you entered. This meets our design principle of Uniform Invoice Software - easy to use. So our invoicing templates here on InvoicingTemplate.com are designed in a what-you-see-is-what-you-get way. To create new invoices with Uniform Invoice Software you don't have to deal with many complex forms that are not intuitive. But having separate city, state and zip has its own advantage, for example it is easy to manipulate the data such as sorting by state name or zip codes.

The disadvantages of this approach is that, as detailed on the top of this simple invoice template, the user must always enter the "city, state zip" text in this format, or the Excel formulas may not be able to split the city, state and zip code properly.

Another way to archive this goal is that make the "oknCustomerCity", "oknCustomerState" and "oknCustomerZip" cells manually fillable, and then concatenate and display the result on the printable invoice. This is implemented in another simple invoice template here on InvoicingTemplate.com.

Like other form designs here on InvoicingTemplate.com, Simple Invoice Template - Splitting City State ZIP is free (offered to you at a price of 0.00USD). It works on Windows XP and all newer versions of Windows, and Excel 2007, 2010, 2013 or 2016.

Format and Specification

Template#c4072
NameSimple Sample - Splitting City State ZIP
CategorySales Invoice Template
Release DateSaturday, May 7, 2016
Format (XLS or XLSX).xlsx
Excel Version Excel 2003 / Excel 2007 / Excel 2010 / Excel 2013 / Excel 2016
Feature Gallery
Columns4
Lines12
Line Height (Points)18.00
Print Area$D$2:$K$47
Papaer Size / OrientationPortrait
Default Margins (Points)
Left22.68
Right22.68
Top45.35
Bottom45.35