Excel 2010: Merge Cells (Concatenation)
While working on spreadsheet you might want to merge cells values into single column or row, which are widely dispersed in the spreadsheet. With Excel CONCATENATE function you can easily merge columns, rows and data values. It takes location of cells or data values as argument. In this post we will be applying this function to merge only two columns.
Launch Excel 2010 spreadsheet on which you want to apply concatenation function. For illustration, we have included a spreadsheet containing fields; First Name, Last Name, and Course.
Now we need to merge the content in First Name and Last Name columns. First we will be adding a new column and label it as Full Name.
We will be using a simple function that will concatenate the content.
The syntax of the function is;
=CONCATENATE(text1, text2….)
we will write it as;
=CONCATENATE(B2,” ”, C2)
In function arguments, B2 and C2 are the locations of data cells which we want to merge, and the double quotation marks with space inside “ ” refers to enforcing a single space between the values.
The function will merge the content of both fields in field Full Name, as shown in the screenshot below.
Now drag the plus sign at the end of the cell towards the end of the column to apply this function over the whole column.
you can also check out previously reviewed Excel function; DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, and LEN.
51 Comments
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Hi, please help me to solve the issue. Thanks.
I got data with 2 lines in one cell and need to change to one line in order to use formula. Even try to unwrap it, still show 2 lines. I try concatenate but the result still same 2 lines. Is that any way to change to 1 line?
HI
put this formula in C3
=TRIM(SUBSTITUTE(SUBSTITUTE(B3,CHAR(13),””),CHAR(10),”, “))
This will result in: [first line,second line], you can then use text to columns with , separator to break the cell into two columns. I believe you can adapt it to a range, right?
Thanks. CLEAN also can remove line breaks.
Is it possible to Concatenate Merged Cells with Non-Merged Cells.
For Example you have a header 2016 over 4 cells (e.g. A1:D1) and beneath you have 4 Different Categories like Q1 in A2, Q2 in B2, …
My problem is that it is not possible to concatenate A2-D2 with the header.
The function shall work, even if somebody inserts a new column in a few months.
— A — B — C — D
1 2016
–
2 Q1 Q2 Q3 Q4
–
Hi! How can i ask you about the formula of Totals error #VALUE?
Who can help me?
what do you mean exactly? you are getting an error when you?
I need to merge numbers and text together. I can’t seem to merge the two.
what seam to be the problem?
a tad too late i guess tho
I’ve been working with a document that someone else created and had already added multiple concatenate formulas too and was unable to get anything more that the formula to reveal itself. I had been reviewing message boards for the past 2 hours when I finally read this post in the comments and it is the ONLY thing that worked. Thank you!
Looking for help in concatenation of text with date formatted output ex. John Smith11-Dec-14. What I get is John Smith12/11/2014
Hi Tina,
if we say that in your sheet you have 3 columns A, B and C with First, last and date fields, and you want to get your John Smith12/11/2014 in column D.
then in D2 =A2&” “&B2&” “&TEXT(C2,”DD/MM/YYYY”)
note that the above works if (and only if) the date values in column C are stored as Date (format) rather than text
As you can see below.
I would like to keep my to do list on the 29th and not follow to 30th.
How can I keept the data I enter on 29th only?
Thank you
Hi Christina,
I am sorry but i can’t quite get do want exactly.
apparently you re using some kind of an excel based calender, probably with some code in it as well?
what i can see is a calender where you select day by day, and you have the 29th opened with 3 entries. Not sure what will happen when you select the 30th, this is a custom built (or available on the internet) template.
can you be more specific?
How do I remove the two columns I had used to concatenate, without altering the column that is the result of the concatenation? For the project that I am working on, now that I have combined that data into one cell as desired, I don’t need the two extra columns that preceded it. But every different thing I try always messes up the results, since i know I’m essentially taking away the data from which it was derived. This can’t be an uncommon problem, but I can’t figure out what to do. Please help!!!!
Hi Amanda,
Select (highlighted) the concatenate column you created, copy, and directly go to paste menu and select paste values
Then delete the source columns
Oh my word, it worked…thank you SO much, Ahmad! I wasn’t even sure anyone would answer!! I *knew* there had to be a way.
Thanks again.
If column C contains hyperlinks. When concatenated, can the hyperlink be retained?
HI, as per my knowledge that’s not possible. and in Excel, generally a cell can contain 1 hyperlink only.
you can do it with shapes, e.g: insert a couple of shapes into a cell and assign separate hyperlinks for each. it looks like a manual process, definitely can be automated with a a VBA maco
I have been trying to include a comma between the cells of information that I’m merging into one cell and haven’t been successful so far. Example, I want to merge individual cells that show a name, street address, city, state, zip into one cell but I need to have commas in between each of these items. John Doe, 111 Main Street, New York, NY, 12345. I need it to look like this once all of the individual cells are merged together. Any easy answer? I’ve been trying to use the concatenate formula and it merges them all together but I need the commas. Any advice is welcome! Thanks!
sure, probably a late reply
=concatenate(Name,”,”,city,”,”,State,”,”,State,”,”,zip)
the items you want to concatenate are in columns, starting from A2 to E2 for an example, and yo want the concatenated output in column F, you would have something like:
F2 =concatenate(A2,”,”,B2,”,”,C2,”,”,D2,”,”,E2)
generally speaking, to add text into concatenate (or generally speaking, any excel formula), you need to put in between “text”
I’m using the Concatonate function thanks to your help page. I’m taking text from up to seven columns and putting them together with a dash (-) separating the text. Everything works great, but if the last few columns do not contain text, I’ll end up with a series of dashes at the end of the string.
Is there any way to delete the dash if that particular cell is blank?
thank you
sure,
its definitely longer but doable, check it and let me know if you need help with it
Anyone try this recently in Office 2010? I can’t get it to work..
Office 2010 english? if so it should work
How do I concatenate multiple cells (30 cells) into a single one? Do I have to state each and every cell? I have almost 3000 sets of 30 cells each. Please help!
=CellA&CellB&CellC&…
or google ASAP utilities
Wow…thanks anyway!
Is there a way to pull the first character from one column (such as the first initial from a first name) and concatenate it with the last name column?
Sure!
see attached image
Any suggestions on how to move a subsequent cell to another line, to read
A N other
17 High ST
Anytown
GB6 4AS
you can use offset() function, google it, sorry cant help more since your question is rather vague on how the output should look like
Hi everyone
I need your help please to save me a lot of work.
I have a large spread sheet with 7000 lines of data (which is about 2500 companies)
Company names appear 2, 3, 4, 5 or 6 times (on consecutive lines when sorted by name) which is why there are 7000 lines of data
There are 15 fields per line of possible information
My problem:
The data has been taken from several other spread sheets and put onto a Master one
Out of the possible 15 fields of inputted information
Some lines with the same company name only have 5 or 6 fields with any data in it
But the data is not necessarily in the same 5 or 6 fields as the other fields on the other lines with the same company name
MY REQUEST
If a company has 5 lines of data all in different fields, I want to merge the 5 lines into just ONE LINE and when the merging process (within lines of the same company name appear) comes across a field within the 5 lines that is empty, it uses the information from one of the other 5 lines to create JUST ONE LINE that has as many possible fields completed, from the merging of the 5 lines.
I hope this makes sense?
Is there a solution please?
Thanks and kind regards
Mike
PS. If there is a way, can I highlight the entire spread sheet and perform the same task in a single go?
man, can you give a sample of one or two companies with different info locations ? before and after (maybe manually just to show us what is needed)? change the name of the company and all other information to dummy one! although the general idea is there, yet the details are messy slightly (to me at least)
Hi Ahmed
Thanks for your reply.
How do I send you a sample for you to see what I mean please.
Thanks
Mike
if you like send me on my email asqbloker(at)hotmail. or, for others in this forum to respond, you can take a snapshot pictures (before and after ) and send it here as a commnet
All that happens for me is that the formula i type (or paste) ends up as text in that cell; the formula doesn’t actually get applied 🙁
Yea, got the same problem. I have a range of cells that link to an external workbook (on our network).
The link looks like this: =’\10.0.100.1ServerSales[Master Price.xls]Landed Cost’!B6
There are many cells linking (ie B7, B8 etc),
As the filename or location of the source document can change, I want to name a specific cell in the Destination document with the link
=’\10.0.100.1ServerSales[Master Price.xls]Landed Cost’!
Problem is, if I join text (Concatenate, or whatever other way), the destination cell only displays the text, without linking to the source document.
Any ideas how to have this cell link?
Thanks!
Does not work for dates. How do I concatenate text and dates without the dates being converted to a number. Changing the date to text did not work.
I know this is a late reply, anyway, sure you can, concatenate text and dates while preserving Date format:
If Dates are in column A with format (M/D/YYYY), and Names/other text in column B, use:
= Text(A1,”M/D/YYYY”)&”,”&B1
check attached
In the formula: =CONCATENATE(text1, text2, text3,….) one should not use , for seperation but ;So the correct formula is =CONCATENATE(text1; text2; text3;….)and =CONCATENATE(B2,” ”,C2) should be =CONCATENATE(B2;” ”;C2)
want to put in place concatenation. i.e want put result in same column again.
e.g. want join A2 and B2.
put result in A2 and delete column B
Thanks.. Simple function.. But helped @ needy 🙂
Using the word CONCATENATE did not work. It did not let me put together enough cells, said I had too many. But using the top solution (omit “concatenate” and just use &) worked for me
Using the word CONCATENATE did not work. It did not let me put together enough cells, said I had too many. But using the top solution (omit “concatenate” and just use &) worked for me
Using the word CONCATENATE did not work. It did not let me put together enough cells, said I had too many. But using the top solution (omit “concatenate” and just use &) worked for me
promotion for paid plugin, but it works. None of the formulas worked.
you can see how to merge cells on excel in:
http://www.thebestdata.com/zoom.aspx?menutype=1&auto=2189
typos..-theword*
the same result from the the concatenate formula can be achieved by actually removing the concatenate world!I.e, for the same function above, you can use: =”text1″&text2″&….we will write it as; =B2&” ”&C2and we will get the same results.
Can it be done so that the next cell is on another line — as you would if you wanted to change a horizontal row of cells with an address in to a single box?
If i understood your question correctly, you would want to concatenate cells from different row, which is possible, you can simply use:
=B2&” ”&C3 (notice different row numbers)
or you can use the offset function, like:
=A5&”-“&OFFSET(A5,1,1)
take a look at attached photos