How to Create a Code 39 Barcode Formula with a Modified Final Character in Excel
Question details
The user wants to generate a Code 39 barcode where the visually displayed text is preserved, but the scanned result introduces a break or modification right before the final character.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating custom Code 39 barcodes for inventory or scanning workflows where the encoded data format must differ slightly from the raw displayed text.
- Observed behavior
- The correct formula cannot be determined without specific sample inputs and expected outputs, requiring a custom string manipulation approach to properly encode the barcode.
Ensure you have a Code 39 barcode font installed on your system (such as 'Free 3 of 9') and identify the exact sample input alongside the expected scan result.
Define Requirements and Construct the Custom Barcode Formula
To create a formula that modifies the final character in a scan result, use Excel's text manipulation functions wrapped in Code 39 start and stop characters.
Code 39 barcodes require specific formatting to be readable by scanners. Specifically, the data must begin and end with an asterisk (*).
If you need to introduce a break or modify the final character without changing the original text cell, you must combine string functions like LEFT, RIGHT, and CONCATENATE.
Determine your exact input string and what the scanner expects. For example, if your input in cell A2 is 'ITEM123' and you want the scanner to read 'ITEM12-3'.
Use Excel text functions to isolate the final character and insert your desired break. For example: LEFT(A2, LEN(A2)-1) & "-" & RIGHT(A2, 1). This drops the last character, adds a hyphen, and appends the last character back.
Wrap the modified string in asterisks (*). The complete formula becomes: ="*" & LEFT(A2, LEN(A2)-1) & "-" & RIGHT(A2, 1) & "*"
Select the cell containing your new formula and change the font to your installed Code 39 font from the Excel Home tab.
Generate Code 39 Barcodes Easily in WPS Spreadsheet
WPS Spreadsheet provides full support for advanced text formulas and custom barcode fonts, allowing you to generate custom Code 39 barcodes effortlessly while ensuring complete compatibility with Microsoft Excel files.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet document containing the raw data.
- 2. Enter the custom formula: In an empty column next to your data, enter your custom string manipulation formula wrapped in asterisks, e.g., ="*" & LEFT(A2, LEN(A2)-1) & "-" & RIGHT(A2, 1) & "*".
- 3. Apply the barcode font: Select the formula cells, go to the Home tab, and choose your installed Code 39 font from the font dropdown menu.
- 4. Print and scan: Adjust the font size to ensure the barcode lines are distinct, then print your spreadsheet and test it with your barcode scanner.
Frequently Asked Questions
Why does my Code 39 barcode not scan even though it looks correct?
Code 39 barcodes strictly require start and stop characters to be readable by a scanner. Ensure your Excel formula wraps the data in asterisks, such as ="*" & A2 & "*".
Can I hide the asterisks in the displayed text under the barcode?
The asterisks must be part of the encoded string for the scanner to read it, so the font will display them. If you want human-readable text without asterisks, keep the raw text in a separate adjacent cell and format the barcode cell without text below it.
How do I install a Code 39 font to use in my spreadsheet?
Download a free Code 39 TrueType font (.ttf) file from a reputable source, right-click the file, and select 'Install'. You will need to restart your spreadsheet software for the new font to appear in the font list.
