hi there jamie here today at teachers tech hope you're having a great day today today i want to
show you how you can combine cells in microsoft excel so what i mean is you could have whether it
be numbers or text and combine them into one so here's a little example what i want to show you
let's say i have a name and then i have a number i can combine it to make it into this so i can
even add more and then i can take this and copy it down just like that so these are some of the
tips of what i want to show you today on how to combine cells text and numbers in microsoft excel
so for this lesson we're not going to be using the concat or concatenate function we're going to be
using the ampersand to combine this cell over here with a number in this cell so how we do this is
we're going to start with our equation sign here so our equal sign and we're going to just select
the first cell and then we're going to use the ampersand sign which is above the 7 shift 7 and
now we click on our next cell and i'm just going to hit enter so as you can see it's not quite
exactly what i showed you in the intro we need to do a few more things to it so the first thing that
we could do is give a space in between the r and the three here so we can do this by going back up
to the formula bar up here so i'm just gonna go ahead and select here and i'm going to type in
uh just the quotation marks here and i'm going to put a space i just hit space bar put another
quotation mark and now i need to add another ampersand sign and i'm going to hit enter so by
doing that now if i click on it this space between the quotation marks acts as the space here so what
i'll if i wanted a dollar sign on this to where it says the 350 000 i could go up here and i'm going
to place it right here so i have a space but then i'm going to do my dollar sign hit enter and now i
have a dollar sign right there i'm going to come back and show you how we can format this number
uh in a bit here but i'm just going to continue on to show you that we can add an ampersand
sign at the end too so if i wanted to add that per episode i just can go up to the top here
and this is where i can add another ampersand sign and use when you're typing you need to use
your quotation marks so i'm going to say per episode but i want a space so i'm going to add
a space first and i'm going to say per episode just like this and i'm going to go quotation mark
and i'm going to hit enter so now i have winona writer 350 000 per episode just by using the amper
ampersand sign and using the uh the quotation marks to put spaces now there's something
else i want to do i could copy this all the way down just by double clicking and filling in
but i'm going to go back one step because i want to add formatting now so this is a cool part of
this because i want to add a formatting to this 350 000 where i have the comma in the correct
spot and i'm going to have the dollar sign so i'm going to actually remove the dollar sign from here
because i'm going to use formatting for this and what i'm going to have is i just go we
are going to use a function too but before i go grab uh put the function in i'm going to grab
some formatting and to do this you can right click anywhere uh in here so i'm just going to
right click and i'm going to go to format cells you can see it right here and i'll drag this up
and where you need to go to custom you can see all the things in here we're going to actually
copy formatting out of here and so in the case of the number here that i want in it it's going to be
it's going to have a dollar sign i wanted to be formatted and i wanted to have comma
so if i look at this here this is you can see there's going to be negative positive
how it's going to be formatting i could take the whole thing and move it over i could just take a
chunk so what i'm going to do is just highlight what i need here and i'm going to just go ctrl c
for copy so i'm going to have this i'm going to hit ok now and a shortcut for this if i go back
to it if i just hit ctrl 1 that's the shortcut that will get you there in a hurry too so two
ways to do that now if i go back to this cell i need to add the text function and where i'm going
to add it is before the b2 so b2 is the number and i'm just going to start by typing text and
you can see the functions come up and i'm going to just select text it adds the the bracket for me
and on this side of the b2 i need to add a comma if you follow along you can see where it says
comma next and i need to add a quotation mark so i'm going to be pasting in that
information that i copied from the formatting so i'm going to go ctrl v and now i'm going to go
and do another quotation mark to end it and i need to close my bracket so when i close my bracket i'm
just going to go ahead and hit enter and you can see now this is formatted with a comma to separate
the thousands and it has the dollar sign in there so at this point i can copy this all the way down
and it copies all the down wake down correctly with the formatting in here also
but we're not done here yet here i want to show you a couple other things that you can do with
formatting numbers and copying text and numbers to get combining them so what i want to show
you now is how you can add text and have it not recognized so what i mean by this is let's say
so i have a formula simple sub formula here here's my total of everybody here uh 2.6 million of all
of these and then i have nine episodes and i just divided it again so much per episode but if i was
going to go to here and go in this cell and if i started typing episodes uh in here so that's built
incorrectly so if i just type episodes click off it doesn't recognize it because it becomes text
you can see how it's aligning there's having some issue in here but you can actually add text here
so if i look at this you can see what's typed up here but i'm going to go and actually format it
so i can have words in it so if i go back to this number nine here and i'm gonna use control one the
shortcut go to custom and this time so it's just gonna be i'm gonna use the zero but i can actually
add my own custom text formatting here and so what i'm going to do is add a quotation mark here
i'm going to add a space and i'm going to type episodes like this and then i'm going to end
my quotation marks so if i hit ok now it says nine episodes and it didn't wreck the formula
so if i look up in the formula bar up top it still says nine so i'll do that one more time
if i click on this one where the amount is this is already formatted with the dollar sign i'm to go
ctrl 1 and i'm going to go to custom and maybe i just want the word total so i can see there
if i go up and just pick a simple one here you can go up and see all the different ones that
you can pick but if i was just picking this and i'm going to pick total i can use my quotation
mark and i write total and i'm going to give a space an end quotation mark and now i have total
the 2.6 million dollars and nothing if i look up at the sum it's still there the text isn't there
it's being recognized as a number so that's a way that you can add text to a cell that doesn't get
recognized as a number and with any of these cells that you just formatted just go back i'm going to
use ctrl 1 again check out the custom and notice that it's in here so this is a format that's
created and if you didn't want it you could delete it and you can see how it goes back to normal like
that so i just want to show you one last thing here quick thing to end this video here today i'm
going to show you how you can copy uh your format from a different cell over to that text function
so you can see in this case i've combined if you look at the formula bar it's the same thing that
i showed you before but it's coming out as 0.85 so i could if i wanted to make sure i got the
same as this what i could do if i click in this and then use my control 1 or go to format cell
and then notice that it's at 85 percent shows me the sample if i go to custom it carries over
at that percent so what i need to do is highlight what i need to copy so i'm just going to go ctrl c
go back over to this one go in front of the b2 and i'm going to go and i put my text function in like
i showed you before and at this point i need my comma so i need my quotation mark i need to paste
in that zero percent what i showed you before i need to end my quotation mark just like that and
end my bracket and hit return or enter and you can see that now it's 85 percent and it copied
the format from there so i just want to show you that one last tip about when we're putting the uh
the formats into that text function so i hope you like this way of combining cells here uh
let me know what you think if it's working for you uh down below in the comments thanks
for watching this time on teachers tech i'll see you next time with more tech tips and tutorials