Excel: Merge cells without losing data

If you’ve ever attempted to merge cells that hold data in Excel, you will almost certainly have encountered this warning:

Image of warning box:
Microsoft Excel
Merging cells only keeps the upper-left value and discards other values.

[OK][Cancel]

This can be annoying when you’re trying to combine data in multiple different fields, such as first and last names. Happily, there is a formula (in Excel, there’s more or less always a formula) to help us out: Concatenate. Here are a few examples of how to use it:

  • When combining two fields without the need for any additional punctuation or space:
    • =CONCATENATE(A1&B1)
  • When combining two fields, wanting to add a space between them:
    • =CONCATENATE(A1&" "&B1)
  • When combining two fields, wanting to add both space and a comma between them:
    • =CONCATENATE(A1&", "&B1)

These examples work when you want to combine multiple fields, too: Here I’ve combined three fields, adding space and punctuation between them: =CONCATENATE(A1&","&B1&" "&C1)

Now, Concatenate is all well and good, but did you know that you can achieve the same result by simply removing it, as follows:

  • When combining two fields without the need for any additional punctuation or space:
    • =(A1&B1)
  • When combining two fields, wanting to add a space between them:
    • =(A1&" "&B1)
  • When combining two fields, wanting to add both space and a comma between them:
    • =(A1&", "&B1)

By posting a comment, you consent to our collecting the information you enter. See privacy policy for more information.

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