Excel: How to easily join text columns with a formula!
Say someone sends you an Excel document and you while they gave you lots of columns of information, you really need some of those columns joined. Let’s say you also don’t want to merge them permanently. You can do it with an easy formula!
This is also called “concatenation” and can be found in the Excel help — but who is going to remember THAT?
In the example below, we are going to join the first name and the last name together in the last column called “full name”
Start with the basic formula: =A2&B2. When you hit return, it will join the two columns and display “Marilyn Monroe:
This isn’t bad. However, we’d really like to create a space between the two names. We can do this by adding a space between a bit of extra code — =A2&” “&B2
To copy this formula to the rest of the column is easy. You have a couple of options:
Just copy the block where we added the formula. Now highlight the empty blocks where you want the code to be applied and paste.
Or you can double click the block until the little handle bar (dot) shows up in the bottom right of the block. Now grab that handlebar and drag it to encompass the rest of your column.
EXTRA TIP: If your Excel document is using all uppercase, you could add a proper case argument to have it display correctly.
- Proper Case: Using =PROPER(A2&” “&B2) would change MARILYN MONROE to Marilyn Monroe