Retrieving Character Segments from a Barcode Field

EAN-13 barcodes are the most common form of retail barcode in the UK, Europe and the rest of World. Yet having a string 13 characters long may not prove very useful when it comes to filtering or grouping similar types, or ranges of products for example.

In this instance it may be that comparable products or items share the same last character or a particular substring of characters. When this is true you can make use of a substring expression to pull these characters out of the overall barcode and into a separate field for filtering and grouping purposes.

Random e.g. barcode: 1099148769077

Barcode Field Name: [Barcode]

If the last 3 characters '077' are common to a group of products you could use the below substring expression to extract these last 3 characters:

if(len([Barcode]) < 13, '', substring([Barcode], 11, 3))

The if(len([Barcode] < 13, '' portion is a validity check to ensure the barcode is of correct length (len), in the case it is less than 13 then no value will be returned. Opposingly the last portion 11, 3 instructs at which character to start from in the string and how many of the following characters to take from there. i.e. start at the 11th (0)  and take 3 inclusive of the 11th (077).

This substring can then be stored in a separate Text Field and used for filtering and grouping purposes.