Join text from column A with text from column B using the concatenation character. This video is from Chapter 9 of Excel 2010 In Depth
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 and 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.
=A2+B2, but since this is text, it's not a + sign, it's the &. 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, ofcourse, the problem here is, that it didn't put a space between there.
So, we want to go back, edit that formula, press F2 to edit.
=A2&" "& So this is saying, hey, we're joining three things together.
Whatever is in A, whatever's in the quotes, which in this case is a space, and whatever is in B. Now take that text and smash it together.
Now, you'll notice that the incoming data was all in uppercase.
And so ofcourse, 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 Paul Mccartney in here just to point out that the second C of 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 in 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 out my last name, fix the few that have rums.
And a fast, fast way to go.
All right hey, I want to thank you for stopping by.
Will 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 and 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.
=A2+B2, but since this is text, it's not a + sign, it's the &. 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, ofcourse, the problem here is, that it didn't put a space between there.
So, we want to go back, edit that formula, press F2 to edit.
=A2&" "& So this is saying, hey, we're joining three things together.
Whatever is in A, whatever's in the quotes, which in this case is a space, and whatever is in B. Now take that text and smash it together.
Now, you'll notice that the incoming data was all in uppercase.
And so ofcourse, 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 Paul Mccartney in here just to point out that the second C of 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 in 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 out my last name, fix the few that have rums.
And a fast, fast way to go.
All right hey, I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
