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;


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.

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


    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:


        take a look at attached photos

  • Ahmad



  • Adam

    you can see how to merge cells on excel in:

  • 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: =’\[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
      =’\[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?


  • 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

    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


    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.

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


      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

      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


      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

      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?

  • tina

    Looking for help in concatenation of text with date formatted output ex. John Smith11-Dec-14. What I get is John Smith12/11/2014

    • Ahmad

      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

  • Chris Booker Sarwine

    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!

  • Meeks

    I need to merge numbers and text together. I can’t seem to merge the two.

    • Ahmad

      what seam to be the problem?
      a tad too late i guess tho

  • Savong Pov

    Hi! How can i ask you about the formula of Totals error #VALUE?
    Who can help me?

    • Ahmad

      what do you mean exactly? you are getting an error when you?

  • Sunny Lim

    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?

    • Ahmad


      put this formula in C3


      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?

      • Sunny Lim

        Thanks. CLEAN also can remove line breaks.