From Chapter 9 of "Excel 2010 In Depth", learn how to join text from column A with text from column B using the concatenation character.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel 2010 in Depth, chapter 9.
Joining Text.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, from chapter 9 in Excel 2010 in Depth, one of my favorite examples is how to join two columns that have text in them.
So, I have first name in column A, last name in column B.
I need to join the two of these together and we use formulas all the time to add two numbers together equal A2 + B2.
But, since this is text, it's not a plus sign.
It's the ampersand (&), the 'and' sign.
So, =A2 & B2. We'll take the text from A, the text from B and smash it together into a single cell.
Now, of course the problem here is that it didn't put a space between there.
So, we want to go back edit that formula, I press [ F2 ], to edit. =A2&"[ space ]"&.
So, this is saying hey! We're joining three things together whatever is in A, whatever is in the quotes.
Which in this case is a [ space ] and whatever is in B.
Now, take that text and smash it together.
Now, it... You'll notice that the incoming data was all in uppercase and so of course my full name ends up in uppercase.
Great function to use here or something called proper, or the proper function will take the text that's in uppercase and convert it to upper and lowercase.
Capitalizing the first letter of every word.
So, the proper function double-click to shoot that down.
Through Palomar Cart in here just to point out that the second 'C' McCArtney, doesn't get fixed.
There's solutions to this.
I don't like any of the solutions, some people say put a [ space ] back here between the two 'Cs' and column B.
I think that looks really bad.
Some people start out, =IF the left of B2,3 is [ MCC ], then do this big dance.
You know what, just use the formula, sort of my last name fix the few that have problems and fast, fast way to go.
Hey, I want to thank your stopping by.
We'll see you next time for another netcast, from MrExcel.
Excel 2010 in Depth, chapter 9.
Joining Text.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, from chapter 9 in Excel 2010 in Depth, one of my favorite examples is how to join two columns that have text in them.
So, I have first name in column A, last name in column B.
I need to join the two of these together and we use formulas all the time to add two numbers together equal A2 + B2.
But, since this is text, it's not a plus sign.
It's the ampersand (&), the 'and' sign.
So, =A2 & B2. We'll take the text from A, the text from B and smash it together into a single cell.
Now, of course the problem here is that it didn't put a space between there.
So, we want to go back edit that formula, I press [ F2 ], to edit. =A2&"[ space ]"&.
So, this is saying hey! We're joining three things together whatever is in A, whatever is in the quotes.
Which in this case is a [ space ] and whatever is in B.
Now, take that text and smash it together.
Now, it... You'll notice that the incoming data was all in uppercase and so of course my full name ends up in uppercase.
Great function to use here or something called proper, or the proper function will take the text that's in uppercase and convert it to upper and lowercase.
Capitalizing the first letter of every word.
So, the proper function double-click to shoot that down.
Through Palomar Cart in here just to point out that the second 'C' McCArtney, doesn't get fixed.
There's solutions to this.
I don't like any of the solutions, some people say put a [ space ] back here between the two 'Cs' and column B.
I think that looks really bad.
Some people start out, =IF the left of B2,3 is [ MCC ], then do this big dance.
You know what, just use the formula, sort of my last name fix the few that have problems and fast, fast way to go.
Hey, I want to thank your stopping by.
We'll see you next time for another netcast, from MrExcel.
