How to join text from two columns using the concatenation character. Episode 412 shows you how to do concatenation, plus how to convert upper case to proper case on the fly.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
During this holiday week again I'm running back through some of my old favorite tips.
Today we have a whole bunch of names in column A, first names in column A, last name in column B and I need to join those together into column C. Now this is a true story, I ran into someone at my old job, who had five thousand rows of this data and her approach was just to start typing all the data, which was take… going to take her days.
The actual trick here is to use something called concatenation.
If we go to Excel help and search for concatenation, I'd show you how to do this, my problem is no one ever uses the word concatenation, so why would you ever think of looking there.
Instead of using a plus sign to add A2 and B2, we need to use the ampersand, that's the Shift+7.
So =A2&B2 and it will smash those values together into a single cell.
Now you're saying: well, hey Bill, you need to have a space between the two.
So I'm going to edit that formula.
=A2&” “&B2 and now we have our data.
To copy that formula down, just double click, the fill handle and it will look to the left and copy the formula down to the right number of rows.
Now I remember, when I actually did this, the lady said: well, this is fine, but I would like to have the text in upper and lower case instead of, you know, screaming the name.
There is 351 functions in Excel and one of the functions we can use here is called the PROPER function.
So =PROPER(A2&” “&B2) and copy that down, and we're good to go.
Now you have to be noticed here, Paul McCartney doesn't get the C in McCartney, right, the second C, you have to watch out for a few of them.
Any name that starts with Mc and then a C, it's not going to do those correctly.
99% of the time though it gets them right, you just have to go through and fix the few anomalies.
The PROPER function, there's also an UPPER function to convert to uppercase and a LOWER function to convert to lowercase.
Hey, thanks for stopping by, we'll see you next year for another netcast from MrExcel.
During this holiday week again I'm running back through some of my old favorite tips.
Today we have a whole bunch of names in column A, first names in column A, last name in column B and I need to join those together into column C. Now this is a true story, I ran into someone at my old job, who had five thousand rows of this data and her approach was just to start typing all the data, which was take… going to take her days.
The actual trick here is to use something called concatenation.
If we go to Excel help and search for concatenation, I'd show you how to do this, my problem is no one ever uses the word concatenation, so why would you ever think of looking there.
Instead of using a plus sign to add A2 and B2, we need to use the ampersand, that's the Shift+7.
So =A2&B2 and it will smash those values together into a single cell.
Now you're saying: well, hey Bill, you need to have a space between the two.
So I'm going to edit that formula.
=A2&” “&B2 and now we have our data.
To copy that formula down, just double click, the fill handle and it will look to the left and copy the formula down to the right number of rows.
Now I remember, when I actually did this, the lady said: well, this is fine, but I would like to have the text in upper and lower case instead of, you know, screaming the name.
There is 351 functions in Excel and one of the functions we can use here is called the PROPER function.
So =PROPER(A2&” “&B2) and copy that down, and we're good to go.
Now you have to be noticed here, Paul McCartney doesn't get the C in McCartney, right, the second C, you have to watch out for a few of them.
Any name that starts with Mc and then a C, it's not going to do those correctly.
99% of the time though it gets them right, you just have to go through and fix the few anomalies.
The PROPER function, there's also an UPPER function to convert to uppercase and a LOWER function to convert to lowercase.
Hey, thanks for stopping by, we'll see you next year for another netcast from MrExcel.
