Merge data columns into one column in Excel
Merge data columns into one column in Excel
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.
- hal_nesbitt
- Site Admin
- Posts: 32
- Joined: Thu Jul 24, 2003 5:53 pm
- Location: Alexandria, VA
- Contact:
"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:
Dragging and dropping the formula:
The finished merged columns:
This will save you a lot of time, so you can get back to watching YouTube. Enjoy!
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:
Dragging and dropping the formula:
The finished merged columns:
This will save you a lot of time, so you can get back to watching YouTube. Enjoy!
"Maturity is stupid." - Benjamin Franklin
Who is online
Users browsing this forum: No registered users and 0 guests