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.

Advertisement
  • Ahmad

    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&” ”&C2

    and we will get the same results.

    • Hamish

      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?

      • Ahmad

        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

  • Ahmad

    typos..

    -the
    word*

  • Adam

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

  • FarVision

    promotion for paid plugin, but it works. None of the formulas worked.

  • Manivannan

    Thanks.. Simple function.. But helped @ needy :)

    • Webdoc46

      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

    • Webdoc46

      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

    • Webdoc46

      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

  • Mwoord

    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)

    • khemraj

       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

  • Hanne

    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.

    • Ahmad

      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

  • dannwebb

    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 :(

    • LeonBrazil

      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!

  • Mike

    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?

    • http://profiles.google.com/ahmed.qadah Ahmed Qadah

      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)

      • Mike

        Hi Ahmed
        Thanks for your reply.
        How do I send you a sample for you to see what I mean please.
        Thanks
        Mike

        • http://profiles.google.com/ahmed.qadah Ahmed Qadah

          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

  • Hamish

    Any suggestions on how to move a subsequent cell to another line, to read
    A N other
    17 High ST
    Anytown
    GB6 4AS

    • Ahmad

      you can use offset() function, google it, sorry cant help more since your question is rather vague on how the output should look like

  • Melanie

    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?

    • Ahmad

      Sure!

      see attached image

  • Van Houton

    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!

    • Ahmad

      =CellA&CellB&CellC&…
      or google ASAP utilities

      • Van Houton

        Wow…thanks anyway!

  • Pat

    Anyone try this recently in Office 2010? I can’t get it to work..

    • Ahmad

      Office 2010 english? if so it should work

  • Paul

    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

    • Ahmad

      sure,

      its definitely longer but doable, check it and let me know if you need help with it

  • Kimberly

    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!

    • Ahmad

      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”

  • Christie

    If column C contains hyperlinks. When concatenated, can the hyperlink be retained?

    • Ahmad

      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

  • Amanda

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

    • Ahmad

      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

      • Amanda

        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.

  • christina

    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

    • Ahmad

      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?