Using TEXT function to format bar code data

The Excel TEXT( ) function is a very powerful and flexible way to format cell values; text, number, date, etc.

TEXT(value, format_text)

The formatted result of the TEXT( ) function can be used with bar code functions, to automatically print bar codes containing exactly the characters you need.

Tip: Before you use bar code functions, use the TEXT( ) function to format results as text in cells, and when you get the formatting you want, then use the results in your bar code formulas.

Leading zeros

Tip: If we use leading zeros, to produce fixed length data, any resulting bar codes will all be the same length.

This formula prints values in A1, formatted with up to 5 leading zeros and no decimal places;

  =TEXT(A1, "00000")

If A1 contains 12 the result is 00012. We can use the result with a bar code function;

  =CIA_CODE128(TEXT(A1, "00000"))

to produce a Code 128 bar code containg 00012.

Date cells


If the cell contained date 07/04/01, the result would be 20010704, which allows numeric only bar code types to be used (e.g. Interleaved 2 of 5);


this also produces smaller printed bar codes.


Copyright 2000 CIA (BAR CODES) UK. All rights reserved. Reproduction prohibited.