Merge data columns into one column in Excel

Questions about running any kind of software, such as how-to's and fixes for MS Office, Adobe Acrobat, etc., or how to do stuff in Windows.
Post Reply
stry
Posts: 3
Joined: Sun Jun 10, 2007 4:22 pm

Merge data columns into one column in Excel

Post by stry » Sat Aug 25, 2007 4:19 pm

So I am working with an address spreadsheet, and there is a separate column for first name, middle initial, and last name, address, etc. What I need to do is combine the first name, middle initial, and last name columns into one column, entitled "fullname". Is there any way to easily merge the data? I tried the merge button, and it will only keep the data in the left cell. Please help! There are over 5,000 entries and I really don't want to have to do a huge cut-and-paste job.

User avatar
hal_nesbitt
Site Admin
Posts: 32
Joined: Thu Jul 24, 2003 5:53 pm
Location: Alexandria, VA
Contact:

Post by hal_nesbitt » Sat Aug 25, 2007 5:55 pm

"Cut and paste"? Dude, this is the 21st century. Excel has a very easy way to merge data in this fashion, using the '&' operator in a formula to join two text fields. Ok, lets say for instance, you have the three names in columns A, B, and C and we are going to merge the data to column D. What we need to do is set up the initial formula. The '&' will merge the data, but we need to add spaces and a '.' after the middle initial. This can be done by inserting them in the formula surrounded by " " marks. Ok, so lets set up the initial formula:

In cell D2, enter:

=A2&" "&B2&"."&" "&C2

This is saying "merge cell A2, and space, and cell B2, and period, and space, and cell C2"

This should merge all three, plus the period after the initial and spaces to the new cell. Good right? Well, that takes care of one cell, and we need to apply this relative formula to the rest of the column. To do that, we need to drag and drop the formula.

Place your cursor in the lower right hand cell of D2 until it become a small black cross, and then drag down until it highlights all cells in the column that you wish to merge the data. When you release, it will apply the relative formula to all those cells and merge the columns. Voila!

Below are a few pics to help:

Setting up the intial formula:
Image

Dragging and dropping the formula:
Image

The finished merged columns:
Image

This will save you a lot of time, so you can get back to watching YouTube. Enjoy!
"Maturity is stupid." - Benjamin Franklin

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests