Build Excel Barcode Generator From Scratch

No VBA ,just formulas, it is easy let me show you how to build a barcode generator in Excel !! Below are the links to the downloadable version of the 20 digit barcode generator.
If your font is not working then try downloading this link and installing it to your windows fonts:

https://drive.google.com/file/d/0B8wC...

Any size barcode generator: https://drive.google.com/file/d/0B8wC...

Here is the link to wikipedia's chart of codes:
http://en.wikipedia.org/wiki/Code_128

I have other Excel videos on barcode generators:
http://youtu.be/gLzH-stj_34

http://youtu.be/4tu99g5e-58

---

Closed Caption:

ok folks today we're going to be
building a bar code generator very soon
41 teach you guys into the to knowing
how to do this and we're going to start
off with the number one two three four
five six
ok that will make some stuff confusing
make it the stuff simple
it's a number of chosen all right so
it's a six-digit number
we want to turn this into a bar code
so I'm going to start by dividing
everything up into its own digits
I want to one pulled out here and put
into this spot so we're going to hit
equals M ID parentheses
what business is asking us where we're
going to rush it's just numbers and a
reference it out of a 10
ok
and myself
- dollar signs in there to make it
permanently a tin
okay then put a comma behind that one
because first position and it's one
digit long another print sheet hit enter
so we pulled that one magically out and
put it over here
now click that box and pull this down
for an additional five boxes because you
have five more numbers to pull out one
two three four five
no it's just pulling out the one doing
it six times you have to change
this is one here
the first 1 into a 2 and it will pull
out the second digit
that's where it gets confusing is we're
not telling it to give us a -
we're saying pull it from the second
position to pull this one from the third
position at a 10 and so on to dividing
this number up into its individual six
digits
ok so for this one we find that the
mouse to work
oops I made it 15 that's wrong
we need to be the fifth digit ok
and the sixth digit and by the way i do
not have a list that is my microphone
and I don't know why it sounds like I
have a speech impediment but
every time I make that s noise makes me
sound funny but at least it did on the
last version this video
so this is the sixth position and we
have a divided up into its
individual cells
now
each
these numbers is worth a value
it's on a point system one is actually
worth 17 points on the chart but
in
we can do we look
out of chart right so click on our a - E
- okay this is where you're going to be
putting your chart and then go to Google
and look at wikipedia code 128 k as I
had violated at the top and the first
result probably will be this but that if
you're seeing this you need to go to
middle of the page i think it starts and
highlight the entire chart
yes internet will let you take the
information I like it all
right click it one time hit copy
go back to your workbook
you're on
now in a cell a1 and right click it and
click the paste option if you hover your
cursor over it's right here
it's called keep source formatting click
that and now you have the chart now
don't want to do this all kinds of
different ways i find it simpler just to
to start over here and start typing in a
wholly separate chart what I need
ok i would just highlight the column I
all the way over way out into the
distance
ensure that highlights most of where
everything else that's on your chart
right let me see if I can get back to
our supposed to be
ok i highlight everything else them on
this page for the sheet to write and
then I would just right click it and it
format shells and I formatted as text
first tab and then you hit txt and hit
ok then I will proceed to type all this
stuff in
ok that is the smartest way to do it
there are other ways to do it it seemed
smart at first but are not
ok so just trust me as we need to do
enter the numbers
for the least for this very first column
you have to enter the numbers by hand
ok so i'll start typing these numbers in
and we'll fast forward and we'll teacher
zero enter 1 enter
ok
we have been to one of the six and that
is where you need to stop and go back up
to the top now
you can shortcut the this column here in
this column here
he so choose to or you can just look
over and type the information in as
follows
I'm steadfast way because i know i had
to take this column for whatever reason
I don't know
formatting purposes probably but the
next column you can enter this people's
te xt that's text parentheses
this is scooter we can see
we got this first column copied over by
hand to hear the second column is code
128 a
and we're gonna need it to copy the text
so copy what's in this
so you reference b3 t comma 0
parentheses
that's just how this works at the put 0
because you need at least one position 0
represents a position if it were three
digit long ago 0 0 0 but it's
III , 0 ok
and there you'll noticed it that doesn't
work because this is also a matter of
this text so we gotta go back and format
these two columns which will be using
this technique in all in general
so which format cells person is general
hit of k
and then replace that with equals get to
retype that equals text parentheses
it's asking me for the cells is just 1
comma 0 shift parentheses enter now it's
brought an overall brought over the only
the text from that she'll
ok so let's do that to the one to the
right I'll sorry that's not going to
work
double-click that down
sorry and it brings over all the symbols
now for this one we're going to bring
over this column over here you'll see
why singing
ok so y equals x parentheses of this
shell , for one digit long basically in
parentheses and enter and brought over r
0 will meet you
now we can double click that cross hairs
all the way down and get all 106 boxes
and now we can get back to this this
thing that said state is merely
representing the space but if they told
you there's a space your trust us you
wouldn't believe
so for the stage here one space hit
enter
that's important ok now i'm going to go
down to where the list starts getting
ruined by just Alice charts with the
other or services in UL online 67 right
yeah she's annual that's the first box
you need to start racing at you need to
their contents for all these five shells
ok the their contents right click their
content now you can hit equal
those texts parentheses or this ' box
here which is he
67 I'm a zero ship friend she's center
now your friend here a little
' they were here double-click that all
the way down the rest of them and brings
over all the stuff we really gave me a
check for where it starts screwing up
with the code again
so he had a key is de el that's sucky
start at go down right click clear
contents
now in will be working in this area you
see that funky e that is what we want to
bring her to here in this box
ok so you can do that one of two ways
you can either copy and paste just that
digit without the space on it here or
you can simply k equals ghar just makes
the little blue box but the first
practice is on 4 u equals character 200
shit parentheses
now see back on de carácter 200 you did
not want to use this little one unless
you do using one of the other codes
sometimes and depending on what your
scanner reader is something screwed up
you may have to switch up to one of
these lower case from PS letters
okay but we're using the first one
before the / karen so that character 200
equals character 200 entered now you
want to double click that with the right
cross hairs all the way around and bring
you lunch ease over so that didn't work
for the rest of the first one there
changes 200 201 and so on
so this one of each -
so e 3 SB character 24 x seven
now there's somebody get this chart it
wasn't all cheer up
I probably know about it already
but if somebody else finds one that's
actually perfect like you'll see this
one is at the end
then tell me we're looking for a chance
like an absolute information on to
everybody else
9
I don't know you oh I'm gonna drag it
formula down
sorry
nine that's right to the chin
this next one and 211 will be the last
one you need
okay that's as far as the killer is
I don't do this at the reverse detection
stuff that's not for us
all right now so now you have a a usable
chart
so let's highlight that old chart
they're like everything nice and need I
put it in borders all borders and then I
pick the color yellow and I highlighted
for you
so it's nice in break ok so all the name
/ it just for organizational sake
well it real short
this is a real chart ok so now we have
the real chart a lot of work just to
come back to the first agent hopefully
all of our formatting is correct and
that shirt
we go to look at this number
sup and it's not formatted great will
not freaking work
ok so now if you've done it all
correctly at this point exactly how i
showed you this should work equals be
looked up hitting the blue and it will
type it out for you
you're referencing this box you just
made which is a be in , asking you what
chart you will bring it off of the
bitchy to wear your chart is and you're
going to be referencing the second and
third column but it will recognize it as
a secondary , recognize it as the first
and second
okay that may be using some others
already know what i'm talking about
highlighting the second and third
columns are real chart
ok this point will give the mouse and
hit enter to burn here that the best way
to do that
don't ask me was just this out is the
into your formula for the , and you're
telling it that you want full
information out of the second column of
the two columns you highlighted people
too , and you want an exact match so
either type balls or pick it will give
you an exact match
also the maximum involved in this
context ok
it just means exact match or not at all
now it should be equal to look up be 10
sheet to pasa fee or ! a 33 l 109 , -
fault
enter there we go
success it looked up the one
what the value of one is on the charger
and 17
we need to pull that formula down for
all 16 and it automatically brings over
the good stuff all the information we
need to easy worse than 80 degrees or
the 19 that's all on that chart
okay now a little bit of math the one
is the first position so all times this
number by one so we'll put equals this
box
losophy one that means x 13 10 x 1
equals 17
this is the second number 18 gets
multiplied by two so let's drag this
formula down and just change of the
multiplication each one
not by one you want times x to enter
want this one x x 3 hear this
this is position for it's not just
because it's the number four is because
it's in the fourth position and this is
the fifth number
inter initially the fish number
interview times in it by its position in
the number so you get all this all the
ships left and you need to add it all up
ok so this is this is how you do it you
need to add a hundred and three
that's the magic number two everything
here
so this is going to equal one of three
plus this box
Plus this box plus this 1 plus this 1
plus this 1 plus this 103 plus
everything above it
enter weeks makes 530 ok now
530 it's / our magic number 103 and the
remainder that's left over is what we
want to get excel to do this you need to
type equals mod or just take it
and now you are going to reference the
shell that we added up in 530 over here
is the key 17 ,
what are you / magic number one of three
and put a parenthesis on it and hit
enter
you get 15 or three gives him so many
times and then remainder is 1515
represents the value of our check digit
but instead of a value
we need a symbol and the symbol is on
the chart within the first and second
column of the chart so we go equals B
look
click the option asking you what cell
referencing the 15
of course what table are you doing it on
after you hit the , well that's on
sheet2 and it's the first two columns of
the real church so highlight all that
well the first two columns over anyways
finger off the button and just hit enter
there's an error that's what you wanted
to do
double click it and put a comma behind
it
so you're at the end of your formula and
you're going to pull your information on
the second row seat to , and you want a
exact match so you pick the option of
bolts which makes no sense to anybody
once you have that hit
No
what do we do
around here we'll try picking the chart
again
something went wrong there
ok
I must a reference something wrong
right next door
online there something all right pick
again enter
ok now we're into a formatting problem
this is what I was talking about when
you don't hand type stuff
ok so it's looking for a 15
and it's looking for it on the charger
let's go to the church and
ar15 for some some reason then
unbeknownst to me
- 15
is a value
going to need
to be a text
ok
so let's try something type in 15 in the
inner
does that section now because it once it
has a value
so we will it always happens opposite if
you pick one of the beginning it through
some point you will want the opposite
so we're doing right is just the shallot
you have to end up doing it
it seems retarded if I put a 0 in here
no let me highlight all this
we highlight all this will turn back to
general number
yeah if you if you made it a text at
first you have to turn back to general
number if you had a general number
you're going to turn back to text at
this point it's just a pattern i've been
noticing the format cells to a general
number
okay now put 0 in here
hit enter
now we'll double click this all the way
down with the crosshairs
click click and will this little box
that pops up with it
the fill series and that will
that will put it all
in there now let's see factor hit it
should hit that
there we go and that's how you do it now
this is your check digit is we've been
working for the whole time
now we need to assemble the code
the first thing you need
equals
is the funky d ok so you can do that one
of two ways you need to find it in the
when you hit insert
and then go to the symbol over here and
then pick it
if you use the ASC to hex symbols or you
can just do it on my show you
equals
and then hit she to go down and find the
funky d
at the end of your charge to force one
from the bottom
hit enter okay that brings
the funky d so this is saying
referencing then the sheet too , away
sorry shift 7 which makes the and symbol
for assembling a string
ok the next thing you need is whatever
is in this cell
our number right shift 7 which is the
and symbol and we need our check digit
which is always going to end up in this
cell so reference it and hit and now we
need to end the equation so that the
scanner will know it's ending
so what does she - and we'll pick the
funky oh the very last letter
that at the hundred and six . hit enter
and now we have it
funky d is the starting character the
funky was the ending character
everything in between is your number
Plus check digit which is that / okay
now watch this
anything I put this will will change and
magically appear here in code
ok so a seven eight seven eight seven
eight and six digits long
enter now pick the check digit s
symbols it funky d the number check
digit and the ending funky number
ok so wherever you put here is that
turned into UPC code but it is not in
UPC bond
so this so we put equals and we just
reference this
the cell
which is g sixteen hit enter now you've
brought it down
he click
at once right click it one time make
sure you get that right
hit formats l
it fault and go down to code 128
this is fun
120 h there it is and it shows a preview
it okay
now right click it one more time and
choose a font size of 48 probably make
it big enough for your screen to see now
you'll notice your box is highlighted it
runs over a couple watches into other
shells right
ok click outside of that now what I want
you to do is get your smartphone out
download an app called barcode scanner
this will help you check your work right
on the screen of your computer is your
computer screens nice enough
so you know and it works on those old
monitors mostly - if you have it
big enough for the sea so you can just
hit ctrl + to scroll forward button will
make it bigger like
this
you want in
go over
so if i did put it in the center of my
screen like this and then I check my
work and it
trust me it will work ok so what can you
do with this magical thing now that he
paused the video thousand times and
majors formula
let me show you so I'm going to add a
sheet
I had a sheet and light foot text here
in in yellow box
enter
and then they'll put their stuff in box
in the right here and then
down below here is where we're aiming to
put our bar codes so 1 2 3 4 5 6 i type
into there and I go back to sheet 1
instead of having this year i put equals
equal
whatever is in this yellow wash enter so
now it brings the number from another
page and all this ugly stuff is hidden
- III again now you want this shell to
equal
no she one where you got your code your
plain old code want to equal that they
highlight that box and hit enter and it
comes at all funky right here so you
right click it is you know you need to
put in the code 120 h and the font size
is like four th
and click it and there you go anything
you put inside of this box will come up
to 32 33 enter changes right before your
eyes now you get to see it so working
its magic
there's another six digit number enter
and she change right before your eyes
Gina's box goes down one cell so if
you're smart
you just move that down one shell that
way when you hit
sir it will go down one box but the area
where you have your UPC barcode here is
not didn't have a box over top of it is
that will interfere with the read
so you have a little insulation between
the two now you have it you can put this
on
worksheets and hide it in the back where
all the magics going on
they put all this stuff on here we have
a sheet the name is this
out of order ok
on it is my formula sheet 2 is my
reference page where I have the chart
real chart off to the right and she
three is mine
kind of my cover sheet hey put your
whatever in the yellow box six digits
long
ok then WPC here and you can figure out
all kinds of creative ways to make that
look fancy but that is the bare bones of
doing a six-digit UPC barcode and if you
wanted a video on how to do one that
detects the size and make it any side of
bar code you want we can do that
just like the video subscribe or
whatever and leave a comment below
telling me you would actually see that
cause I don't know maybe people don't
want to see how to make that
but it yeah and if you have friends like
this kind of stuff
post to Facebook you co workers might be
interested in how to do this
maybe
maybe know some people that are just
into this kind of stuff
I'm putting it out there because almost
nobody else has the
formula way to make this only tell you
in some workplaces you do not have
access to macro
yeah
that kind of Big Time stuff so if you
have to do something all my formulas but
no vba code or whatever you fancy pants
one call it
then you have to go formulas and this is
pure formulas
this is and i'll be typed I can take
this out of my memory
I know where to get all the symbols from
online and
you can even have to download to
computer at work and and have your
company worrying that you're downloading
something that computer
everybody has itself almost every
company out there in every copy of the
shell has code 128 in and some of them
have code 39 which is much more simple
but most of them do not because this is
the newest version of excel in most
companies won't pony up and a lot of
them are working on the 2003 version of
Excel still but if you want to go all
formulas
this is the way to go I get it all
written out for you
I think a 5th grader could probably pull
this office has been as i have explained
it so like it subscribe
comment she later