Skip to main content main content

Invoice Template for Holland Clothing (Wear) Company or Shop

- Document for Excel Invoice Manager

 Share This Page

This version of the document, "Invoice Template for Holland Clothing (Wear) Company or Shop", was written for Excel Invoice Manager, which was now renamed to Uniform Invoice Software. For the latest version of this document, please download Uniform Invoice Software.

The sample invoice template implemented here is based on C6-001 for clothing / wear company, who needs the "Size" column. Most text labels in this invoice template are translated into Dutch.

In Holland zip code contains a space, like "1234 AA" and it is normal to put the ZIP first and then the name of the city. It is always written as: ZIP CODE space CITY, like: "1234 AA CITY". For example, the "City State ZIP" cell/field in the "Bill To" section looks like "1234 AA Amsterdam. It is same in the "Ship To" section.

To display this type of "City, State ZIP", we can use Excel formulas. There are two steps involved to implement this feature based on the invoice format provided by C6-001.

Tip: Download the customized invoice template for holland clothing (wear) company or shop from the bottom of this page.

Note: Invoice Template for Holland Clothing (Wear) Company or Shop was revised and published on our new web site, Invoice Templates, at Netherland Sales Invoice Template and Netherland Service Invoice Template. Excel Invoice Software has published the custom build for these templates too, where the customized sample Netherland invoice templates become the default invoice template of the Uniform Invoice Software installation. Refer to Netherland Service Invoice Template for Excel Invoice Software and Netherland Sales Invoice Template for Excel Invoice Software for more information.

Note: Invoice Templates has also created two variants and customization samples for Invoice Template for Holland Clothing (Wear) Company or Shop. These new samples moved the "Size"("Maat" in Dutch) column out of the printable invoice form and added VAT# cell to both the Customer and Invoice Header database tables, making the new invoicing templates more general and suitable for more business types. Please refer to Netherland Sales Invoice Template 2 and Netherland Service Invoice Template 2 for more information and download links of the sample Netherland invoice templates. For custom builds that integrate the new sample Netherland invoice templates as the default invoice template of Uniform Invoice Software, visit Excel Invoice Software, at Netherland Sales Invoice Template 2 for Excel Invoice Software and Netherland Service Invoice Template 2 for Excel Invoice Software.

Step 1 - Move cells

First, we should move out the default "City, State ZIP" cells - the cells names are oknWhoCityStateZip and oknShipCityStateZip. The steps to move cells are as follows.

  1. Unprotect the Invoice worksheet.
  2. Drag your mouse to select all the cells/fields you want to move.
  3. Push your mouse key on the border of the selected area; drag it to the new location.
  4. Once finished, protect the Invoice worksheet.
  5. Save the template.

To move a control:

  1. Unprotect the Invoice worksheet.
  2. Switch to design mode.
  3. Click and drag the control to the new location you like.
  4. Once finished, exit design mode.
  5. Protect the Invoice worksheet.
  6. Save the template.

Step 2 - Create formula to display city, state and zip in correct format

The formula to analyze the original "City, State and ZIP" format and return it in the Holland format is:

SUBSTITUTE(oknWhoCityStateZip," ", "^",LEN(oknWhoCityStateZip)-1-
LEN(SUBSTITUTE(oknWhoCityStateZip," ","")))))," ",
LEFT(oknWhoCityStateZip,FIND("^",SUBSTITUTE(oknWhoCityStateZip," ","^",
LEN(oknWhoCityStateZip)-1-LEN(SUBSTITUTE(oknWhoCityStateZip," ",""))))-1))

The most important Excel worksheet functions used here are as follows, as detailed in Microsoft Excel documents.

  • SUBSTITUTE (text,old_text,new_text,instance_num)

    The worksheet function substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string.

    • text is the text or the reference to a cell containing text for which you want to substitute characters.
    • old_text is the text you want to replace.
    • new_text is the text you want to replace old_text with.
    • Instance_num specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

    In this case, the first SUBSTITUTE formula searches oknWhoCityStateZip for spaces, and replace them with a symbol "^", and the number of occurrence is the count of all spaces in oknWhoCityStateZip minus 1. As a result, this SUBSTITUTE formula replaces all spaces in oknWhoCityStateZIP, except the last space, with the symbol "^".

  • FIND (find_text,within_text,start_num)
    • Find_text is the text you want to find.
    • Within_text is the text containing the text you want to find.
    • Start_num specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

    FIND locates one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

  • LEN (text)

    LEN returns the number of characters in a text string.

  • RIGHT(text,num_chars)
    • Text is the text string containing the characters you want to extract.
    • Num_chars specifies the number of characters you want RIGHT to extract.

    RIGHT returns the last character or characters in a text string, based on the number of characters you specify.

  • CONCATENATE (text1,text2,...)

    It joins several text strings into one text string. Text1, text2, ... are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.

Sometimes, understanding how a nested formula, such as the one provided here, calculates the final result is difficult because there are several intermediate calculations and logical tests. However, by using the Evaluate Formula dialog box, you can see the different parts of a nested formula evaluated in the order the formula is calculated.

  1. Select the cell that you want to evaluate. Only one cell can be evaluated at a time.
  2. On the Formulas ribbon tab, in the Formula Auditing group, click Evaluate Formula.
  3. Click Evaluate to examine the value of the underlined reference. The result of the evaluation is shown in italics.

    If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.

    Note - The Step In button is not available for a reference the second time the reference appears in the formula, or if the formula refers to a cell in a separate workbook.

  4. Continue until each part of the formula has been evaluated.
  5. To see the evaluation again, click Restart.

    To end the evaluation, click Close.

To use this invoice template

When you enter "City, State and ZIP", enter it as normal into the oknWhoCityStateZip cell ($S$10) and oknShipCityStateZip ($S$11), it will then shown in Holland format in the "Bill To" and "Ship To" section.

Related invoice templates

  • Invoice Template for Clothing Retail Shop (C6-001)

    The template adds a custom field "Size" to both the Product and Invoice Body (InvBdy) database tables. For example, if you have a clothing retail shop, it is necessary to stock sizes like 2xl 3xl 4xl 5xl 6xl - each product will have these sizes and the size information should be added to invoices too.