Function Problems

How to Extract GS1 Barcode Fields Using Excel Formulas

Anum Mushtaq Oct 15, 2026 868 views

Question details

The user needs a reliable method to parse and extract specific data fields from complex GS1 barcode strings using Excel formulas.

How to Extract GS1 Barcode Fields Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Extracting various Application Identifiers (AIs) and their corresponding numeric or alphanumeric values from a single GS1 barcode text string.
Observed behavior: Simple FIND formulas return incorrect results, zero-length strings, or #REF!/#VALUE! errors when application identifiers repeat (e.g., '17' appearing multiple times) or when variable-length fields and group separators are present.

Before you start

Before applying formulas, identify the exact Application Identifiers (AIs) your barcodes use (such as 01, 17, 21, or 10) and check if your scanner inserts visible brackets around them or invisible Group Separator (GS) characters.

Solution 1Recommended

Use MID and FIND Formulas to Extract Fixed and Variable Fields

This is the standard approach to locate specific Application Identifiers (AIs) and extract the subsequent data based on its known length or its position relative to the next identifier.

GS1 barcodes string together multiple pieces of information. By combining Excel's MID and FIND functions, you can pinpoint the start of an identifier and extract the characters that follow.

Assuming your barcode string is in cell A2, use the following steps to extract specific standard fields.

Extract the 14-digit GTIN (AI 01)

Select the cell where you want the GTIN to appear and enter the formula: =MID(A2,FIND("01",A2)+2,14). This finds '01', skips the 2 digits of the identifier, and extracts the next 14 characters.

Extract a Fixed-Length Date (AI 17)

To extract a 6-digit expiration date following the '17' identifier, select your target cell and use: =MID(A2,FIND("17",A2)+2,6). This will pull exactly 6 digits.

Extract Variable-Length Values Between Two AIs

If you need a serial number (AI 21) that appears before the date (AI 17), the length can vary. Use this formula to measure the distance between the two: =MID(A2,FIND("21",A2)+2,FIND("17",A2)-FIND("21",A2)-2).

Extract the Final Variable-Length Value (AI 10)

For a batch or lot number (AI 10) located at the very end of the barcode string, use the LEN function to capture everything remaining: =MID(A2,FIND("10",A2)+2,LEN(A2)-FIND("10",A2)-1).

Use MID and FIND Formulas to Extract Fixed and Variable Fields

Check Your Identifier Formatting: If your barcode scanner outputs parentheses around identifiers, such as (01) or (17), adjust your FIND text to FIND("(01)",A2) and increase the starting position offset from +2 to +4 to account for the brackets.

Solution 2

Adjust Formulas to Handle Repeated Identifiers and Errors

Use this method if your basic FIND formula targets the wrong occurrence of a number (like finding a '17' inside a batch number instead of the '17' identifier) or returns a #VALUE! error.

Effortless Data Extraction

Extract Barcode Data Easily with WPS Spreadsheet

WPS Office includes a highly capable Spreadsheet application that fully supports advanced text-parsing formulas like MID, FIND, and LEN. It is highly compatible with Microsoft Excel, allowing you to manage and extract complex GS1 barcode data seamlessly.

1. Open Your Data: Launch WPS Spreadsheet and open the worksheet containing your scanned GS1 barcode strings.
2. Apply Text Formulas: Select an empty cell and input your MID and FIND formulas exactly as you would in standard Excel.
3. Drag to Fill: Use the fill handle at the bottom right of the cell to drag the extraction formula down across your entire dataset instantly.

100% compatible with Microsoft Excel formulas and .xlsx formatsFree, lightweight, and fast-loading office suiteAdvanced text processing and data formatting capabilitiesFamiliar user interface with zero learning curve

Frequently Asked Questions

Why does my FIND formula return a #VALUE! error for certain barcodes?

A #VALUE! error typically means the text string you are searching for (such as the identifier '17' or '10') does not exist in that specific barcode, or the formula contains a typo. Wrapping your formula in an IFERROR function can help manage missing optional identifiers gracefully.

How do I handle GS1 barcode scanners that output parentheses?

If your scanner is configured to output Application Identifiers with brackets (e.g., '(01)' instead of '01'), you must update the FIND argument to search for "(01)". You also need to adjust the starting position offset from +2 to +4 to skip the four characters forming the bracketed identifier.

Can Excel detect the invisible Group Separator (GS) in a barcode?

Yes, if your barcode scanner is configured to transmit the ASCII 29 character (Group Separator) to your computer. You can reference this invisible character in Excel formulas using CHAR(29). For example, finding the end of a variable field can be done with FIND(CHAR(29), A2).

Why does my formula grab the wrong numbers when an identifier repeats?

The FIND function strictly stops at the very first match it discovers, reading left to right. If your lot number contains the number '17', and you are searching for the AI '17' further down the string, the formula stops too early. To fix this, use a nested FIND to force Excel to start its search after a later, known character position.

Excel & Data Problems