Screenshots and Images
The printed version.
Open the template in Excel.
You are safe to download the resources. We've added our digital signatures to the files.
Download this if you have already installed the Microsoft Store edition of Invoice Manager for Excel, and are looking for additional customized templates.
Download this if you want this design to be set as the default template by the installer program.
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.
- Backup both your Excel Invoice Template and database file.
- Open the invoice template as usual.
- Click the Design Mode button on Excel "Invoice" ribbon tab. Note that to show the "Invoice" ribbon tab you have to install Invoice Manager for Excel, our Excel invoicing software that leverage both the features of front-end Excel and back-end database.
- 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".
- 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.
- 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".
- 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".
- 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".
- Now exit design mode by clicking the "Design Mode" button again on the "Invoice" ribbon tab.
- Click the "Customers" button on the "Invoice" ribbon.
- Click "Custom Fields", and then "Add Field".
- Enter the name of the field "CustomerCity", choose its type "Text", and set the size to "50". Click OK to create the custom field.
- Click "Add Field" again. Create the second custom field - Name: oknCustomerState; Type: Text; Size: 50. Click OK to create the custom field.
- Click "Add Field" again. Create the third custom field - Name: oknCustomerZip; Type: Text; Size: 50. Click OK to create the custom field.
- Now from the "Database table" list, choose "Invoice Header". Click "Add Field".
- Repeat the above steps to add "CustomerCity", "CustomerState" and "CustomerZip" custom fields for the Invoice Header database table.
- Click "Close" to close the custom field manager.
- Save the invoice template by clicking the Save button on Excel quick access toolbar.
- 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 Invoice Manager for Excel - 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 Invoice Manager for Excel 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.
Format and Specification
|Name||Simple Sample - Splitting City State ZIP|
|Category||Sales Invoice Template|
|Release Date||Saturday, May 7, 2016|
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||18.00|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|
|Price||Free (0.00 USD)|
|System||Windows 7 and later, and Excel 2007 and later.|