Barcodes in Excel (formula to create correct string)

[edjohn20]

@HappyChappy and @Rijnsent did some work on this several years back. I found the old thread but the file have since been removed.

Hi having great problems with bar codes currently using a web based site to generate barcodes (that work just fine) then cutting and pasting them into excel as a picture. The site uses Code 128 for the fonts.

However, I downloaded the code 128 font and thought it would be better to do it that way so I can produce the codes faster, but the codes are different from my down loaded picture. I make a barcode of 123456 but it won't scan. I have a feeling it has to do with the check digits but I don't know where to go from here.

 

[HappyChappy]

BarCodeV2.0.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Order Processing
2	Route/Drop
3
4	Date:					Awaiting Rebook:
5
6	Order No:					Awaiting Cancellation:
7
8	Customer Name:					Rebooked:
9
10	Postcode:					Cancelled:
11
12	Qty of Items:					Instant Return:
13
14	Qty of SLEDGES:
15
16	Processed By:
17
18	Re Book Date if required:
19
20	1st Attempt to rebook Delivery						Attempted By:
21
22	2nd Attempt to rebook Delivery						Attempted By:
23
24	3rd Attempt to rebook Delivery						Attempted By:
25
26	Comments:
27	973467890	Ì973467890JÎ
28
Barcode

Cell Formulas
Range	Formula
B27	B27	=BarCodeInfo!J4

this should sort things out

 

[edjohn20]

BarCodeV2.0.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Order Processing
2	Route/Drop
3
4	Date:					Awaiting Rebook:
5
6	Order No:					Awaiting Cancellation:
7
8	Customer Name:					Rebooked:
9
10	Postcode:					Cancelled:
11
12	Qty of Items:					Instant Return:
13
14	Qty of SLEDGES:
15
16	Processed By:
17
18	Re Book Date if required:
19
20	1st Attempt to rebook Delivery						Attempted By:
21
22	2nd Attempt to rebook Delivery						Attempted By:
23
24	3rd Attempt to rebook Delivery						Attempted By:
25
26	Comments:
27	973467890	Ì973467890JÎ
28
Barcode

Cell Formulas
Range	Formula
B27	B27	=BarCodeInfo!J4

this should sort things out

I am not sure I fully understand. Is there a way to download this sheet so I can view formlas?

 

[HappyChappy]

BarCodeV2.0.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Get font from:							BarCodeTest
2		Code 128 - comments | dafont.com							Input test	975789881
3									Test result	Ì975789881sÎ
4		Barcode 128 Cipher - 128a 128b 128c - Online Decoder, Encoder							Bar Code (font required!)	Ì975789881sÎ
5		id	bands	128A	128B	128C	ASCII
6		0	212222	space	space	0	32
7		1	222122	!	!	1	33	!
8		2	222221	"	"	2	34	"
9		3	121223	#	#	3	35	#
10		4	121322	$	$	4	36	$
11		5	131222	%	%	5	37	%
12		6	122213	&	&	6	38	&
13		7	122312	'	'	7	39	'
14		8	132212	(	(	8	40	(
15		9	221213	)	)	9	41	)
16		10	221312	*	*	10	42	*
17		11	231212	+	+	11	43	+
18		12	112232	,	,	12	44	,
19		13	122132	-	-	13	45	-
20		14	122231	.	.	14	46	.
21		15	113222	/	/	15	47	/
22		16	123122	0	0	16	48	0
23		17	123221	1	1	17	49	1
24		18	223211	2	2	18	50	2
25		19	221132	3	3	19	51	3
26		20	221231	4	4	20	52	4
27		21	213212	5	5	21	53	5
28		22	223112	6	6	22	54	6
29		23	312131	7	7	23	55	7
30		24	311222	8	8	24	56	8
31		25	321122	9	9	25	57	9
32		26	321221	:	:	26	58	:
33		27	312212	;	;	27	59	;
34		28	322112	<	<	28	60	<
35		29	322211	=	=	29	61	=
36		30	212123	>	>	30	62	>
37		31	212321	?	?	31	63	?
38		32	232121	@	@	32	64	@
39		33	111323	A	A	33	65	A
40		34	131123	B	B	34	66	B
41		35	131321	C	C	35	67	C
42		36	112313	D	D	36	68	D
43		37	132113	E	E	37	69	E
44		38	132311	F	F	38	70	F
45		39	211313	G	G	39	71	G
46		40	231113	H	H	40	72	H
47		41	231311	I	I	41	73	I
48		42	112133	J	J	42	74	J
49		43	112331	K	K	43	75	K
50		44	132131	L	L	44	76	L
51		45	113123	M	M	45	77	M
52		46	113321	N	N	46	78	N
53		47	133121	O	O	47	79	O
54		48	313121	P	P	48	80	P
55		49	211331	Q	Q	49	81	Q
56		50	231131	R	R	50	82	R
57		51	213113	S	S	51	83	S
58		52	213311	T	T	52	84	T
59		53	213131	U	U	53	85	U
60		54	311123	V	V	54	86	V
61		55	311321	W	W	55	87	W
62		56	331121	X	X	56	88	X
63		57	312113	Y	Y	57	89	Y
64		58	312311	Z	Z	58	90	Z
65		59	332111	[	[	59	91	[
66		60	314111	\	\	60	92	\
67		61	221411	]	]	61	93	]
68		62	431111	^	^	62	94	^
69		63	111224	_	_	63	95	_
70		64	111422	NUL	`	64	96	`
71		65	121124	SOH	a	65	97	a
72		66	121421	STX	b	66	98	b
73		67	141122	ETX	c	67	99	c
74		68	141221	EOT	d	68	100	d
75		69	112214	ENQ	e	69	101	e
76		70	112412	ACK	f	70	102	f
77		71	122114	BEL	g	71	103	g
78		72	122411	BS	h	72	104	h
79		73	142112	HT	i	73	105	i
80		74	142211	LF	j	74	106	j
81		75	241211	VT	k	75	107	k
82		76	221114	FF	l	76	108	l
83		77	413111	CR	m	77	109	m
84		78	241112	SO	n	78	110	n
85		79	134111	SI	o	79	111	o
86		80	111242	DLE	p	80	112	p
87		81	121142	DC1	q	81	113	q
88		82	121241	DC2	r	82	114	r
89		83	114212	DC3	s	83	115	s
90		84	124112	DC4	t	84	116	t
91		85	124211	NAK	u	85	117	u
92		86	411212	SYN	v	86	118	v
93		87	421112	ETB	w	87	119	w
94		88	421211	CAN	x	88	120	x
95		89	212141	EM	y	89	121	y
96		90	214121	SUB	z	90	122	z
97		91	412121	ESC	{	91	123	{
98		92	111143	FS	|	92	124	|
99		93	111341	GS	}	93	125	}
100		94	131141	RS	~	94	126	~
101		95	114113	US	DEL	95	195	Ã
102		96	114311	FNC3	FNC3	96	196	Ä
103		97	411113	FNC2	FNC2	97	197	Å
104		98	411311	ShiftB	ShiftA	98	198	Æ
105		99	113141	CodeC	CodeC	99	199	Ç
106		100	114131	CodeB	FNC4	CodeB	200	È
107		101	311141	FNC4	CodeA	CodeA	201	É
108		102	411131	FNC1	FNC1	FNC1	202	Ê
109		103	211412	Start A	Start A	Start A	203	Ë
110		104	211214	Start B	Start B	Start B	204	Ì
111		105	211232	Start C	Start C	Start C	205	Í
112		106	2331112	Stop	Stop	Stop	206	Î
BarCodeInfo

Cell Formulas
Range	Formula
J2	J2	=Barcode!$A$27
J3	J3	=CreateBarcode128B(J2)
J4	J4	=J3
G7:G100,G102:G112	G7	=G6+1
H6:H112	H6	=CHAR(G6)

Try this

 

[edjohn20]

What is the reference in j2? it looks like a second spreadsheet

 

[edjohn20]

BarCodeV2.0.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Get font from:							BarCodeTest
2		Code 128 - comments | dafont.com							Input test	975789881
3									Test result	Ì975789881sÎ
4		Barcode 128 Cipher - 128a 128b 128c - Online Decoder, Encoder							Bar Code (font required!)	Ì975789881sÎ
5		id	bands	128A	128B	128C	ASCII
6		0	212222	space	space	0	32
7		1	222122	!	!	1	33	!
8		2	222221	"	"	2	34	"
9		3	121223	#	#	3	35	#
10		4	121322	$	$	4	36	$
11		5	131222	%	%	5	37	%
12		6	122213	&	&	6	38	&
13		7	122312	'	'	7	39	'
14		8	132212	(	(	8	40	(
15		9	221213	)	)	9	41	)
16		10	221312	*	*	10	42	*
17		11	231212	+	+	11	43	+
18		12	112232	,	,	12	44	,
19		13	122132	-	-	13	45	-
20		14	122231	.	.	14	46	.
21		15	113222	/	/	15	47	/
22		16	123122	0	0	16	48	0
23		17	123221	1	1	17	49	1
24		18	223211	2	2	18	50	2
25		19	221132	3	3	19	51	3
26		20	221231	4	4	20	52	4
27		21	213212	5	5	21	53	5
28		22	223112	6	6	22	54	6
29		23	312131	7	7	23	55	7
30		24	311222	8	8	24	56	8
31		25	321122	9	9	25	57	9
32		26	321221	:	:	26	58	:
33		27	312212	;	;	27	59	;
34		28	322112	<	<	28	60	<
35		29	322211	=	=	29	61	=
36		30	212123	>	>	30	62	>
37		31	212321	?	?	31	63	?
38		32	232121	@	@	32	64	@
39		33	111323	A	A	33	65	A
40		34	131123	B	B	34	66	B
41		35	131321	C	C	35	67	C
42		36	112313	D	D	36	68	D
43		37	132113	E	E	37	69	E
44		38	132311	F	F	38	70	F
45		39	211313	G	G	39	71	G
46		40	231113	H	H	40	72	H
47		41	231311	I	I	41	73	I
48		42	112133	J	J	42	74	J
49		43	112331	K	K	43	75	K
50		44	132131	L	L	44	76	L
51		45	113123	M	M	45	77	M
52		46	113321	N	N	46	78	N
53		47	133121	O	O	47	79	O
54		48	313121	P	P	48	80	P
55		49	211331	Q	Q	49	81	Q
56		50	231131	R	R	50	82	R
57		51	213113	S	S	51	83	S
58		52	213311	T	T	52	84	T
59		53	213131	U	U	53	85	U
60		54	311123	V	V	54	86	V
61		55	311321	W	W	55	87	W
62		56	331121	X	X	56	88	X
63		57	312113	Y	Y	57	89	Y
64		58	312311	Z	Z	58	90	Z
65		59	332111	[	[	59	91	[
66		60	314111	\	\	60	92	\
67		61	221411	]	]	61	93	]
68		62	431111	^	^	62	94	^
69		63	111224	_	_	63	95	_
70		64	111422	NUL	`	64	96	`
71		65	121124	SOH	a	65	97	a
72		66	121421	STX	b	66	98	b
73		67	141122	ETX	c	67	99	c
74		68	141221	EOT	d	68	100	d
75		69	112214	ENQ	e	69	101	e
76		70	112412	ACK	f	70	102	f
77		71	122114	BEL	g	71	103	g
78		72	122411	BS	h	72	104	h
79		73	142112	HT	i	73	105	i
80		74	142211	LF	j	74	106	j
81		75	241211	VT	k	75	107	k
82		76	221114	FF	l	76	108	l
83		77	413111	CR	m	77	109	m
84		78	241112	SO	n	78	110	n
85		79	134111	SI	o	79	111	o
86		80	111242	DLE	p	80	112	p
87		81	121142	DC1	q	81	113	q
88		82	121241	DC2	r	82	114	r
89		83	114212	DC3	s	83	115	s
90		84	124112	DC4	t	84	116	t
91		85	124211	NAK	u	85	117	u
92		86	411212	SYN	v	86	118	v
93		87	421112	ETB	w	87	119	w
94		88	421211	CAN	x	88	120	x
95		89	212141	EM	y	89	121	y
96		90	214121	SUB	z	90	122	z
97		91	412121	ESC	{	91	123	{
98		92	111143	FS	|	92	124	|
99		93	111341	GS	}	93	125	}
100		94	131141	RS	~	94	126	~
101		95	114113	US	DEL	95	195	Ã
102		96	114311	FNC3	FNC3	96	196	Ä
103		97	411113	FNC2	FNC2	97	197	Å
104		98	411311	ShiftB	ShiftA	98	198	Æ
105		99	113141	CodeC	CodeC	99	199	Ç
106		100	114131	CodeB	FNC4	CodeB	200	È
107		101	311141	FNC4	CodeA	CodeA	201	É
108		102	411131	FNC1	FNC1	FNC1	202	Ê
109		103	211412	Start A	Start A	Start A	203	Ë
110		104	211214	Start B	Start B	Start B	204	Ì
111		105	211232	Start C	Start C	Start C	205	Í
112		106	2331112	Stop	Stop	Stop	206	Î
BarCodeInfo

Cell Formulas
Range	Formula
J2	J2	=Barcode!$A$27
J3	J3	=CreateBarcode128B(J2)
J4	J4	=J3
G7:G100,G102:G112	G7	=G6+1
H6:H112	H6	=CHAR(G6)

Try this

never mind, i see what is going on with the first and second sheet

 

[edjohn20]

BarCodeV2.0.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Get font from:							BarCodeTest
2		Code 128 - comments | dafont.com							Input test	975789881
3									Test result	Ì975789881sÎ
4		Barcode 128 Cipher - 128a 128b 128c - Online Decoder, Encoder							Bar Code (font required!)	Ì975789881sÎ
5		id	bands	128A	128B	128C	ASCII
6		0	212222	space	space	0	32
7		1	222122	!	!	1	33	!
8		2	222221	"	"	2	34	"
9		3	121223	#	#	3	35	#
10		4	121322	$	$	4	36	$
11		5	131222	%	%	5	37	%
12		6	122213	&	&	6	38	&
13		7	122312	'	'	7	39	'
14		8	132212	(	(	8	40	(
15		9	221213	)	)	9	41	)
16		10	221312	*	*	10	42	*
17		11	231212	+	+	11	43	+
18		12	112232	,	,	12	44	,
19		13	122132	-	-	13	45	-
20		14	122231	.	.	14	46	.
21		15	113222	/	/	15	47	/
22		16	123122	0	0	16	48	0
23		17	123221	1	1	17	49	1
24		18	223211	2	2	18	50	2
25		19	221132	3	3	19	51	3
26		20	221231	4	4	20	52	4
27		21	213212	5	5	21	53	5
28		22	223112	6	6	22	54	6
29		23	312131	7	7	23	55	7
30		24	311222	8	8	24	56	8
31		25	321122	9	9	25	57	9
32		26	321221	:	:	26	58	:
33		27	312212	;	;	27	59	;
34		28	322112	<	<	28	60	<
35		29	322211	=	=	29	61	=
36		30	212123	>	>	30	62	>
37		31	212321	?	?	31	63	?
38		32	232121	@	@	32	64	@
39		33	111323	A	A	33	65	A
40		34	131123	B	B	34	66	B
41		35	131321	C	C	35	67	C
42		36	112313	D	D	36	68	D
43		37	132113	E	E	37	69	E
44		38	132311	F	F	38	70	F
45		39	211313	G	G	39	71	G
46		40	231113	H	H	40	72	H
47		41	231311	I	I	41	73	I
48		42	112133	J	J	42	74	J
49		43	112331	K	K	43	75	K
50		44	132131	L	L	44	76	L
51		45	113123	M	M	45	77	M
52		46	113321	N	N	46	78	N
53		47	133121	O	O	47	79	O
54		48	313121	P	P	48	80	P
55		49	211331	Q	Q	49	81	Q
56		50	231131	R	R	50	82	R
57		51	213113	S	S	51	83	S
58		52	213311	T	T	52	84	T
59		53	213131	U	U	53	85	U
60		54	311123	V	V	54	86	V
61		55	311321	W	W	55	87	W
62		56	331121	X	X	56	88	X
63		57	312113	Y	Y	57	89	Y
64		58	312311	Z	Z	58	90	Z
65		59	332111	[	[	59	91	[
66		60	314111	\	\	60	92	\
67		61	221411	]	]	61	93	]
68		62	431111	^	^	62	94	^
69		63	111224	_	_	63	95	_
70		64	111422	NUL	`	64	96	`
71		65	121124	SOH	a	65	97	a
72		66	121421	STX	b	66	98	b
73		67	141122	ETX	c	67	99	c
74		68	141221	EOT	d	68	100	d
75		69	112214	ENQ	e	69	101	e
76		70	112412	ACK	f	70	102	f
77		71	122114	BEL	g	71	103	g
78		72	122411	BS	h	72	104	h
79		73	142112	HT	i	73	105	i
80		74	142211	LF	j	74	106	j
81		75	241211	VT	k	75	107	k
82		76	221114	FF	l	76	108	l
83		77	413111	CR	m	77	109	m
84		78	241112	SO	n	78	110	n
85		79	134111	SI	o	79	111	o
86		80	111242	DLE	p	80	112	p
87		81	121142	DC1	q	81	113	q
88		82	121241	DC2	r	82	114	r
89		83	114212	DC3	s	83	115	s
90		84	124112	DC4	t	84	116	t
91		85	124211	NAK	u	85	117	u
92		86	411212	SYN	v	86	118	v
93		87	421112	ETB	w	87	119	w
94		88	421211	CAN	x	88	120	x
95		89	212141	EM	y	89	121	y
96		90	214121	SUB	z	90	122	z
97		91	412121	ESC	{	91	123	{
98		92	111143	FS	|	92	124	|
99		93	111341	GS	}	93	125	}
100		94	131141	RS	~	94	126	~
101		95	114113	US	DEL	95	195	Ã
102		96	114311	FNC3	FNC3	96	196	Ä
103		97	411113	FNC2	FNC2	97	197	Å
104		98	411311	ShiftB	ShiftA	98	198	Æ
105		99	113141	CodeC	CodeC	99	199	Ç
106		100	114131	CodeB	FNC4	CodeB	200	È
107		101	311141	FNC4	CodeA	CodeA	201	É
108		102	411131	FNC1	FNC1	FNC1	202	Ê
109		103	211412	Start A	Start A	Start A	203	Ë
110		104	211214	Start B	Start B	Start B	204	Ì
111		105	211232	Start C	Start C	Start C	205	Í
112		106	2331112	Stop	Stop	Stop	206	Î
BarCodeInfo

Cell Formulas
Range	Formula
J2	J2	=Barcode!$A$27
J3	J3	=CreateBarcode128B(J2)
J4	J4	=J3
G7:G100,G102:G112	G7	=G6+1
H6:H112	H6	=CHAR(G6)

Try this

is this =CreateBarcode128B(J2) a stored procedure somewhere or a script? I don't see the formula or code for it

 

[HappyChappy]

Dropbox

www.dropbox.com

 

[edjohn20]

Dropbox

www.dropbox.com

Thank you. I couldn't figure out how to reply to the conversation. As bad as that sounds, but i did not see any form of means to reply to it

 

[HappyChappy]

hope it helps