by From this short article you will learn how to merge multiple Excel columns into one without losing data.
You have a table in Excel and what you want is to combine two columns, row-by-row. For example, you want to merge the First Name & Last Name columns into one, or join several columns such as Street, City, Zip, State into a single "Address" column, separating the values with a comma so that you can print the addresses on envelops later.
Regrettably, Excel does not provide any built-in tool to achieve this. Of course, there is the Merge button ("Merge & Center" etc.), but if you select 2 adjacent cells in order to combine them, as shown in the screenshot:
You will get the error message "Merging cells only keeps the upper-left cell value, and discards the other values." (Excel 2013) or "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only." (Excel 2010, 2007)
Further in this article, you will find 3 ways that will let you merge data from several columns into one without losing data, and without using VBA macro. If you are looking for the fastest way, skip the first two, and head over to the 3rd one straight away.
Merge two columns using Excel formulas
Say, you have a table with your clients' information and you want to combine two columns (First & Last names) into one (Full Name).
- Insert a new column into your table. Place the mouse pointer in the column header (it is column D in our case), right click the mouse and choose "Insert" from the context menu. Let's name the newly added column "Full Name".
- In cell D2, write the following CONCATENATE formula:
=CONCATENATE(B2," ",C2)In Excel 2016 - Excel 365, you can also use the CONCAT function for the same purpose:
=CONCAT(B2," ",C2)Where B2 and C2 are the addresses of First Name and Last Name, respectively. Note that there is a space between the quotation marks " " in the formula. It is a separator that will be inserted between the merged names, you can use any other symbol as a separator, e.g. a comma.
In a similar fashion, you can join data from several cells into one, using any separator of your choice. For instance, you can combine addresses from 3 columns (Street, City, Zip) into one.
- Copy the formula to all other cells of the Full Name column. Or see how to enter the same formula into multiple cells at once.
- Well, we have combined the names from 2 columns in to one, but this is still the formula. If we delete the First name and /or the Last name, the corresponding data in the Full Name column will also be gone.
- Now we need to convert the formula to a value so that we can remove unneeded columns form our Excel worksheet. Select all cells with data in the merged column (select the first cell in the "Full Name" column, and then press Ctrl + Shift + ArrowDown).
Copy the contents of the column to clipboard (Ctrl + C or Ctrl + Ins, whichever you prefer), then right click on any cell in the same column ("Full Name" ) and select "Paste Special" from the context menu. Select the Values button and click OK.
- Remove the "First Name" & "Last Name" columns, which are not needed any longer. Click the column B header, press and hold Ctrl and click the column C header (an alternative way is to select any cell in column B, press Ctrl + Space to select the entire column B, then press Ctrl + Shift + ArrowRight to select the whole column C).
After that right click on any of the selected columns and choose Delete from the context menu:
Fine, we have merged the names from 2 columns into one! Though, it did require some effort :)
Combine columns data via Notepad
This way is faster than the previous one, it doesn't require formulas, but it is suitable only for combining adjacent columns and using the same delimiter for all of them.
Here is an example: we want to combine 2 columns with the First Names and Last Names into one.
- Select both columns you want to merge: click on B1, press Shift + Right Arrrow to select C1, then press Ctrl + Shift + Down Arrow to select all the cells with data in two columns.
- Copy data to clipboard (press Ctrl + C or Ctrl + Ins, whichever you prefer).
- Open Notepad: Start-> All Programs -> Accessories -> Notepad.
- Insert data from the clipboard to the Notepad (Press Ctrl + V or Shift + Ins).
- Copy tab character to clipboard. Press Tab right in Notepad, press Ctrl + Shift + Home, then press Ctrl + X.
- Replace Tab characters in Notepad with the separator you need.
Press Ctrl + H to open the "Replace" dialog box, paste the Tab character from the clipboard in the "Find what" field, type your separator, eg. Space, comma etc. in the "Replace with" field. Press the "Replace All" button; then press "Cancel" to close the dialog box.
- Press Ctr + A to select all the text in Notepad, then press Ctrl + C to copy it to Clipboard.
- Switch back to your Excel worksheet (press Alt + Tab), select just B1 cell and paste text from the Clipboard to your table.
- Rename column B to "Full Name" and delete the "Last name" column.
There are more steps than in the previous option, but believe me or try it yourself - this way is faster. The next way is even faster and easier :)
Join columns using the Merge Cells add-in for Excel
The quickest and easiest way to combine data from several Excel columns into one is to use Merge Cells add-in for Excel included with our Ultimate Suite for Excel.
With the Merge Cells add-in, you can combine data from several cells using any separator you like (e.g. space, comma, carriage return or line break). You can join values row by row, column by column or merge data from the selected cells into one without losing it.
How to combine two columns in 3 simple steps
- Download and install the Ultimate Suite.
- Select all cells from 2 or more columns that you want to merge, go to the Ablebits.com Data tab > Merge group, and click Merge Cells > Merge Columns into One.
- In the Merge Cells dialog box, select the following options:
- How to merge: columns into one (preselected)
- Separate values with: choose the desired delimiter (space in our case)
- Place the results to: left column
- Make sure the Clear the contents of selected cells option is ticked and click Merge.
That's it! A few simple clicks and we've got two columns merged without using any formulas or copy/pasting.
To finish up, rename column B to Full Name and delete column "C", which is not needed any longer.
Much easier than the two previous ways, isn't it? :)
255 comments
Thank you!!
May God continue to bless you for making life more stressfree for us. Wonderful add-on! Keep it up!
ITS REALLY HELPFUL THANKS A LOTS
Thanks you it is really help ful.
Thank you for saving the day!
Excellent!
Excellent
Thank you
excellent
Sir I am Having data in Excel as shown Below
1> ASHOK KUMAR BLOCK C/9 KOLKATA - 700009
2> RAMESH SINGH PLOT NO 123 DELHI
3> RAM KHANA BLD NO 435 MUMBAI-400092
I want data which should look like
ASHOK KUMAR
1 BLOCK C/O
KOLKATA - 700009
RAMESH SINGH
2 PLOT NO 123
DELHI - 110080
RAM KHANA
3 BLD NO 435
MUMBAI-400092
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
I have the following formula to concatenate =CONCATENATE(A2," ",B2) so the result will be as follows: A2 B2 in the same cell.
Once I have that I need value A2 on top of B2 in the same cell.
The problem is that i need the concatenation as this:
A2
B2
I need:
A2 value on top of B2 value in the same cell.
please help!!!
Hello Adriana,
Please try the following formula:
=CONCATENATE(A2,CHAR(10),B2)
When using line breaks to separate the concatenated values, you must have the "Wrap text" option enabled for the result to display correctly. To do this, press Ctrl + 1 to open the Format Cells dialog, switch to the Alignment tab and check the Wrap text box.
Hope it will help you.
Thanks so much! Found exactly what I needed to know and it worked! Great instructions, easy to follow and very well explained.
Excellent!!!
I used this method
"Merge two columns using Excel formulas"
Short and to the point instructions.
Thank you very much
I have rows of email addresses in excel that I want to merge into word so that I can copy and paste into a recipient list for emails, with the semi colon between each one. Any suggestions?
Thanks
Its really nice to work with formula"CONCATENATE". Thanks a lot
Extremely helpful the Notepad trick - it worked wonders!!
(and I used WPS instead of Excel, same thing). THANK YOU!!!
It worked.Saves a lot of time. Thank you.
thanks we got it solved with formula
wow. saved much time. Thanks a lot....
Yep. Worked perfectly. Thanks!
Using this site saved me so much time, I am excited!!! I spent hours on excel trying to merge two fields manually... Thank you so much!!!
Thanks! Really appreciate it!
Hi,
what about date and time? how am I going to merge it using CONCATENATE?
Date Time
7/6/2017 15:17:55
if I will concatenate it, numbers will appear and if I will change the format to date or time, it will not appear as is.
thank you,
Hi, John,
Please note that CONCATENATE requires at least one text string to work properly.
Supposing that you have your date in A1 and the time in B1, use this formula to concatenate them into the one cell:
=CONCATENATE(TEXT(A1,"m/d/yyyy")," ",TEXT(B1,"h:mm:ss"))
Please take a look at this point of our article that explains how to concatenate numbers and dates correctly.
Thank you so much for this! Made my day.
BEAUTIFUL...saved me hours of retyping manually.
Thank you!!
EXCELLENT. I'm an Excel Guru and have been trying to figure out why Microsoft has not added this years ago. A BIG thank you. You saved me so much time.
Really helpful
Thank you! (For In cell D2, write the following formula: =CONCATENATE(B2," ",C2)
I have an urgent inquiry pls :(
I need to merge 2 columns in 1 column but not in the same cell... I need them to be in following cells! Meaning if I have a column A (NAME) & column B (address), I need them to be
Name
Address
Name
Address
etc...
Any suggestions please!
Unfortunately, there's no such formula that would merge the data into 2 different cells of one column. Luckily, we have "Create Cards" that can solve your problem. You can see how it works here and even try it for free for a couple of weeks.
This is a very helpful, thank you!
Sir I need a little help please. I have two column, the first column have text "species name (Italicized) with author name (not italicized)" whereas 2nd column have numbers. when I merge both column, the italic species names becomes non italic too. How can I preserve the unique format of each column intact. As there are hundards of rows thus making spp. name italic again one by one taking lot of time.Thanks
This is a very helpful tutorial
In one column I have D.
ANother column I have 00002
I want to combine these two
as D.00002
I did with =(E5&D5)
gives me result as D.2, I need D.00002
in one column I have 00002 and one column I have D.
I want mergee these two columns, I tried It give s me result as D.2, But I want D.00002
How can I
i want to combine two cell data into one cell with one cell data contain alphanumeric value PMKVY-INVJ- and other one contain numeric value 00000425, how to combine them
i have tried so many methods like Concatnate function, =a1&a2 etc. all the time what i got is PMKVY-INVJ-425, there are no zeroes, what to do?
Need some help please. I have 3 separate cells, each ca contain variable data
e.g. Cells A1 and A2 can contain one of the following P, p, pi, pe,F, f, -, untested, Cell A3 can contain P,p,F,f,-,untested - what I want as a result in A4 is - If A1 and A2 and A3 all contain P or p(i or e also)then ALL OK, IF A1 or A2 or both contain an F or f but A3 = P or p then A4 should read T OK but prob. If Ai and A2 have P,p,pi,pe but A3 is F then A4 should read T OK up to x-point. If A1,A2 and A3 all contain an F or f then A4 should read T F
Hope you can help - Many Thanks
thank you so much sir ....
please suggest 1, 2 3 columns vlooup shortcut
Hello, select three columns and do the vlookup same and enter the vlookup(A1,Table Array,{1,2,3},0)} in last and enter. you will get the data for three columns at a time.
well the final option doesn't seem to work for Mac.
Absolutely amazing! Great explanation! Thank you very much! THis was very helpful.
Awesome work! Microsoft needs to pay you royalties for this one.
This was perfect!! Thank you for the amazing tutorial!
Many thanks. Same as Mike, Brill instructions, thanks
Wow, first time I was ever able to google something like this and get an easy solution that works. Thanks!
Awesome notepad trick! Thanks meistro
Hellow..i have difficulties in excel.
How do i merge several columns into one ? Not concatenating.
Example below (the result intended is in column D:
A B C D (result)
Apple Apple (Blank) Apple
Pear (Blank) Pear Pear
Plum (Blank) (Blank) Plum
(Blank) Grape Grape Grape
(Blank) (Blank) Peach Peach
Kindly need your help...
Thanks...
Hi Junny,
You should use the following formula:
=TRIM(A1 & (IF(AND(NOT(ISBLANK(B1)), B1 <> A1), " " & B1, "")) & (IF(AND(NOT(ISBLANK(C1)), C1 <> A1, C1 <> B1), " " & C1, "")))
Thanks ! It Works.
thank you
The Notepad trick is so simple. It was exactly what I needed to get the job done.
One comment: I found that if I highlighted and copied the tab between two cells after pasting the data into Notepad, I could then paste that into the Notepad search window's FIND field.
Thanks for the excellent help!
This article is really help me to solve a problem. Nice
Exactly the info I needed! Thanks!
I need your help.
I have combined =concatenate(A1," ",A2). but i want "A2" to be bold?
is this possible? I always send email every day but i only change the A2 part.
Hello, rara
Sorry, but you can't apply rich text formatting to the formula result.
Excel doesn't provide such functionality.