Microsoft Excel 2013 Tutorial - 22 - More Cool String Functions Video Lecture | Microsoft Excel 2013: Be the Excel Master - Software Development

welcome back guys to another tutorial
and uh okay that was my impression that
I was going to get a laugh I went for it
and got nothing so might as well just
teach you guys about some more string
functions that way alright so we already
saw a couple cool string functions that
you can do and how you can combine
different pieces of strings in different
kinds of ways but did you know that you
can actually take a bit of text and
combine it with the numerical function
kind of a tongue twister so let me show
you guys what I'm talking about and
you're actually going to be doing this a
lot so what you can do is you can go to
any cell for some reason I pick this one
and start typing in some string so I'll
say that the total value of my store is
dollar sign so of course we already know
that okay we could calculate the total
store in a cell keep this and then
somehow merge the two cells together but
let's just go ahead and do it all in one
shipping so this is our string of text
right here at ampersand which is going
to concatenate or join them with
something and what we can do is actually
throw a numerical function like sum in
here so of course the sum is and I
should have kept that column from like
two tutorials ago where I got the total
value but for now I'll just add up like
any numbers these ones look good again
this is just an example oh whoa so now
whenever I hit enter check it out it
says the total value of my store is and
we combined it it or combined it we
combined it is that the past tense
who knows we joined it should you say
that with a numerical function print the
stinking awesome
so that's one cool thing one quick
sharing function but I just got a call
in and I got some new inventory coming
it's a item called bacon bacon bacon
bacon bacon okay however I'm lazy and I
don't feel like writing the word bacon
five times
so instead I found out about this cool
function right here it's our EPT and
what it does is it okay what it does as
I was saying before the stupid alert
interrupted me is it allows you to
repeat a string however many times you
want so the string that I want to repeat
is bacon and of course the name of this
product is bigger make or break your
Bacon's so that's five times hit enter
and check it out so again that's the
function rept and it's pretty much
asking what string do you want to repeat
what text and what number of times you
want to repeat it
five bega begum a bacon pretty sweet I
got a feeling it's going to be my one of
my most popular products what is it
exactly I have no idea but it sounded
cool all right now here's something else
say that we have I don't know 14 of
these in stock and they cost um I don't
know like four or fifty six each pretty
sweet but what I want is a little
graphic telling me because I'm not you
know very visual with these numbers so I
want a little you know kind of a chart
or visual to demonstrate how much of
each product we have quantity wise I'm
also going to change this because for
this demonstration it's going to be kind
of a pain in the butt alright so what we
can do is I'll do for this I shall do
for this very first one if you hit enter
and call the repeat function again what
we can do is actually mother F R right
hit thought wrong stupid thing again
alright add any symbol instead of a bit
of text and instead of just giving it
arm you know some hard number what we
want to do is we want to reference b3 so
what this is going to do what we're
pretty much saying is repeat this symbol
or print it out this number of times so
why are we doing that well check it out
let me drag this down whenever we do
that what it's going to do is it's going
to give us a nice little visual of
exactly how many items we have in stock
so if we only have two hoodies what's
the only going to print out two little
stars if we have 14 bacon bacon bacon
it's going to put out 14 stars and of
course I know that oh like Excel has a
ton of graphs and you know tables that
you can use to chart everything and
display it nicely but if you ever just
want to do something real quick
I know maybe someone's walking by our
desk then want something like two
seconds well it's a pretty cool look
trick so one of the last things I'm
going to talk about is this um this is
kind of I don't even have a story for
this actually let me go ahead and make
one up for some reason I like teaching
better when I make up a pretend story
other words known as a lie and teach it
so say that we have a new product called
um I love baloney or bolog-nah all right
so some idiot inserted it into our excel
file with a bunch of these stupid spaces
right here and of course we can't be
printing that out on our invoices when
we sell millions of this I love Bologna
we'll say it's like a t-shirt or
something so what we can do is we can
actually use a function called trim and
what trim does is it takes a piece of
data and it doesn't give you a nice
little description right there but it's
basically going to remove any extra
spaces so hit enter and look at what it
does it pretty much removes all of the
extra spaces but not all of them it just
doesn't combine I love Bologna into one
word um like you might think so it
pretty much takes a long sense and it
cleans it up for you now while I have
some extra time I might as well go ahead
and run through the rest of these
character functions or string functions
let's see what's another length so
you're going to be using this one in
this one le n returns the number of
characters and a texturing so if you
ever want to say how many characters are
in this well it's le n then throw
whatever string you want as the
parameter if you are wondering there
are indeed 14 characters in the string I
love Bologna so you know what else do
you need to know in life that's pretty
much solve the mystery to the universe
now for casing arm you use these a lot
with names and I probably should use
this on my website because people always
sign up and they either type the name in
lowercase all lowercase letters like how
stinking lazy are you can't capitalize
the first one or it's all uppercase or
sometimes you just want to format stuff
so I won't even on upper right here what
it does is it changes everything the
uppercase um if you have something like
lower it's going to take whatever string
and change it to all lowercase and just
ending is an example let me put
something like Bucky Roberts and I'll
show you guys this one last one I will
demonstrate this proper what this does
is it actually takes a piece of text and
changes it to proper case so check that
out pretty cool so when you have like a
big data set and everyone enter their
name like a moron you can just throw a
proper function on it and it's going to
fix it out for you and make it look
pretty for your boss or a teacher
whatever and actually since I have time
I thought that was going to be the last
one but I'll talk to you guys about one
more that just popped into my head and
that is substitute so let me put sub
stitute and what this does is it
actually takes three different
parameters the first one is okay that
annoys me because if I ever type back
then I it gives me a stupid error so
you're probably going to be seeing that
a lot but the first one is what is your
original string I'll put something like
um I love my mom so the next parameter
it takes is what word in here or what
part of text you want to replace well I
want to replace mom and the third one is
what you want to replace it with and I
want to replace it with cheeseburgers
so what this does is it takes I love mom
and it replaces mom with cheeseburgers
so the final output is instead of I love
mom I love cheeseburgers
so that ladies and gentlemen is how you
replace text in the string and a bunch
of other cool stuff and I you know I
won't repeat everything because I just
taught it to you if you want me to
repeat it you're probably just to watch
the video again so for now thank you
guys for watching don't forget subscribe
yada yada tomato Plateau see you guys
next time