Have you ever had a list of items in Excel that you wanted in a different format, but you didn’t want to spend the next few days or hours manually typing them into different columns or copying and pasting until your fingers bled? For example, let’s say you have a list of people’s names that you would like to turn into email addresses.
Instead of manually typing out all of the email addresses yourself, make Excel do the work for you.
In this scenario, we have a list of the people that exist at Fake Corp,. but we would like to publish their names with their email addresses. The email address format we want is firstname.lastname@fakecorp.com.
1. We will start by selecting the cell in the column next to the cell containing the name:
2. The function we will call to help us with this is “CONCATENATE”. The formula we will use in this example is =CONCATENATE(K2,".",L2,"@fakecorp.com"). Be sure to include the apostrophes and commas - the formula won't work otherwise.
The first result looks like this:
3. Now the easiest way to fill in the rest of the cells without having to type all that information in again is to use the Fill Handle (my personal favorite). The fill handle is at the bottom right corner of the selected cell:
You may have never noticed it before, but once you get the hang of it, I know you will find it to be very useful. To use it, hover your cursor over the fill handle until your cursor changes to a +. Then, click and drag it down as far as you want to copy the formula to other cells and then release the mouse. Whether it’s 5 cells, 500 cells or 5,000 cells the fill handle will make them all match. The result will be:
You can you use this same method to combine columns in Excel for other reasons, too. You'll just need to adjust your formula to format it how you want - just remember, use quotation marks to enter copy not in a cell (even spaces) and separate with commas - so combining a list of first names in Column A and a last names in Column B will be =CONCATENATE(A1," ", B1).
Now that you know how to combine columns in Excel, learn how to divide columns of information (like a list of names) into separate columns. Read this blog post to find out how.
