1. Home
  2. MS Office
  3. Excel 2010 merge cells concatenation

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.

table names

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.

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.

merge cells 1

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.

full name 1

you can also check out previously reviewed Excel function; DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, and LEN.

51 Comments

  1. 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?

    • 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

  2. 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!

  3. 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

  4. 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?

  5. 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.

    • 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

  6. 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”

  7. 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

  8. 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!

  9. 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?

  10. 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

  11. 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

  12. 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!

  13. 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

  14. 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

    • 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

  15. you can see how to merge cells on excel in:
    http://www.thebestdata.com/zoom.aspx?menutype=1&auto=2189

  16. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.