Easy Excel - Combining Cells - Concatenation (10)

Sharing buttons:

hello everyone welcome to the tenth easy

Excel tutorial part of easy programming

net today I want to show you something

very simple yet a little bit complex

it's very easy to do and it comes in

handy more than you think and it can

save you a ton of time

today's topic is combining cells and the

past I showed you merging Center this

button right here which merges multiple

cells and puts them into one

the thing with multiple what Merchant

Center is that if you have data in more

than one cell like I have here John and

Smith it will overwrite the second cell

it'll only keep whatever whatever is in

the top left cell so click on merge and

center it will it says merging Center

merging cells will only keep the upper

left cell value and describes Delta

values reference okay it gets ready

Smith and I have John sure it merge the

cells but that's not what I want so I'll

just undo ctrl Z and I'm going to show

you how to combine the cells today and

keep both of them it's a little small

little formula so why would you want to

do this well let's say you have a list

of names like I have here first and last

name and they're separated and you want

to put them into a single cell so you

can copy and paste John and Smith and

natasha romanoff you like home and over

and over again into the adjacent column

or you can just use the formula that I'm

going to show you today and save a whole

water tank if you're familiar with

programming you'll know that this the

terminology in this case is called

concatenation most programming languages

have it and so does Excel so let's get

started in front of you we have illicit

names let's say you want to put them

together for whatever reason we know

whether is the job that asks for it or

whatever else you have and if you're

familiar with programming you'll

understand this pretty quickly even if

you're not it's very simple concept so

since this is a formula we're going to

start at C c1 and we're going to start

with the equal sign just like any normal

formula and we want to combine John and

Smith John is in cell a1 so we'll select

a1 and Smith is in a2 b2 b1 excuse me

well so if I select b1 while I'm here

while a one is here it'll just override

a one that's not what we want we want to

pretty much add a1 and b1 together in

this case in Excel the concatenation

symbol is the ampersand symbol so a1 and

you can look at it that way you'd be 1c

now we I can select both a1 and b1 it

pretty much acts you can look at it like

a plus sign but instead of it

it connects two strings together that's

pretty much what concatenation is if I

press ENTER what this will do is will

put John Smith together there you go the

cells are combined right in programming

languages like PHP the concatenation

symbol is docked in C++ as you've seen

if you may have seen in my other videos

it's a it's a less than less than value

you know sir the arrow and Excel uses am

percent and the

put John Smith together without any

spaces because matter these strings here

have any spaces in them so we'll have to

add our own so we'll edit the formula

right here and after the ampersand

symbol we'll put an open quote space and

another open quote and one where I'm

presentable so in this formula where

concatenation concatenating twice so a1

and we're adding a space of our own and

a b1 the reason we're doing quotes is

because we're telling Excel that we're

adding another character or string and

not another formula if I press ENTER it

automatically it enters that space there

if I do it without the space it will

give me an error and it will say and it

will fix it and get rid of one of the

ampersand symbol so it says we found a

typo in front when tried to correct it -

a1 and b1 do you want to extend this

correction I'll press no so I'll just

put the quotes back in there there we go

and press Enter and there you go chance

miss easy way this is a formatting done

- concatenation formula it's just they

have one symbol ampersand and just like

before we only have to do the formula

once and with their friend the autofill

right here I'm going to drag it all the

way down here and look everything lines

up as it should I just have to do it

once see a 2 B 2 a 2 b ta all the way to

United States a 14 and B 14 it's pretty

easy to do but also pretty complex it's

not that complex but if you're showing

this to someone who doesn't know Excel

you'll very likely to impress them a

little bit with concatenation you can

combine as many cells as you want in any

way that you want but remember that it's

just for combining face data so if you

have formulas or anything that you want

to combine the cells with it will not

copy the formulas it will copy the

results if you have 2 + 2 in 1 so + 2 +

3 + another cell and you combine those

two cells it will give you a 4 and a 5

so it'll look like the answer is 45 it

won't actually add 4 + 5 or anything it

just takes it just converts it to


okay so let's text and like I said

before you can come out as many cells as

you want so I have a few more examples

here in New York City and Greenwich Mean

Time so I'm going to as a test just

combine these three so ampersand and and

we're adding whoops after that one more

to go and this so we have your

concatenate in quite a few times because

we're adding two spaces

there you go New York City so this is a

formula a seven plus B seven eight

seventeen b-17 and c-17 with two

concatenations in between right so let's

just autofill there you go we have

grandmas meantime easy as that all you

have to do is remember the ampersand

symbol and excels in Excel and that's

it's the way to concatenate if you've

ever done any programming should be easy

for you whereas there is best there is

that's they're all today we covered

combining data next time I'll show you

how to split the columns like this we

have John Smith to the United States

we'll split them into two different

columns because sometimes you need to

separate first-name and lastname or

whatever data you're working with I

already have that started here

splitting data is another very easy but

also complex tasks it's easy it's always

good to know these things because they

can save you a whole lot of time if you

have any questions please feel free to

ask if you want to see some types of

tutorials let me know thank you for

watching and remember to subscribe to my

channel you can like this video share it

and remember to visit easy programming

that net thanks for watching