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:

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)