How To Merge Cells in Excel Without Losing Data

June 5, 2020

We use Excel sheets for different kinds of projects. If you want to merge cells in excel or combining text from one or more cells is vital when we are editing it. Sometimes we may lose data in this process. Here we guide you with simple steps on merging data cells without losing any data. Let us get started.

How to Combine Text from Multiple Cells into One Cell?

Text from multiple cells can be combined into one cell by using the ampersand (&) operator.

To combine the text in two cells A1 and B1, steps to be followed are as follows:

  • We have to select the cell in which we want to place the combined data.
  • To start the formula, we have to type an equal sign =
  • Then we have to click on the first cell, i.e., A1, then type the ampersand operator (&)
  • If space is needed between the text of multiple cells, type the text string “(double quote, space, double quote).
  • Now click the second cell, i.e., B1 and press enter to complete the formula.

How to Merge Cells in Excel?

To merge the cells in excel, Merge and Center option can be used in the home tab. But using this option, cells can be combined but not the text.

When we select the cells and use the “Merge and Center” option, it will keep the writing from the leftmost cell and remove the text from all other cells.

To merge the cells without losing text, we can use the “CONCATENATE” function. To merge cells without losing text, we can use the “Center across selection” option as an alternative to joining and Center option.

How to combine cells in Excel?

To combine the cells in excel, we have various options.

  • We can use merge and center option
  • We can use the ampersand (&) operator.
  • We can use the CONCATENATE function.

Excel concatenates multiple cells.

Concatenate function helps us to combine the content from multiple cells into one destination cell without retyping all the text. Steps to be followed to concatenate are:

Initially, we have to select the cell in which combined text to be placed. Then type the function into the cell, which starts with an equal sign (=) followed by concatenating.

=CONCATENATE.

Arguments can be entered in two ways,

Method 1

First, we have to open the parenthesis, type the cell references (A2, B2) separated by commas and then close the brackets at the end.

=CONCATENATE (A1, B1). Otherwise, we can click on the A2 cell, type a comma, then click on the B2 cell and then type the closing parenthesis.

If you see an alert message, click on yes.

Now we have to press ENTER after adding the cell references to the concatenate function.

No space is found between the combined texts as the concatenate function combines precisely what is in the arguments.

Method 2

You can also use =A1&B1 to merge shells data

Method 3

=CONCATENATE (A5, “”, B5)

To fill sequential data into Excel with the fill handle, we need not type the function in every cell in the column. We can quickly copy the function to the other cell.

Excel merge cells vertically without losing data

The quick and simple way to combine two or more cells vertically is to use the built-in merge option combined with center option.

  • First select contiguous cells you want combine
  • Select the alignment group from the home tab.
  • Now click on the merge and Centre button.
  • When we click the button, the cells start to combine into a single text
  • There are other options called Merge across Merge cells that work similarly.

Align the text after merging.

  • First, select the text you wish to align with.
  • Now select the desired alignment from the alignment group.
  • This is found on the home tab.

Also Read: How to Merge Excel Files and Sheets

Why can’t I merge cells in excel

Several reasons make cells unable to be merged

The most common reasons are:

  • The cell is in a table
  • The cell is in a protected sheet

When you are cell is in a table, you have to merge it before creating the table.

When your cell is in a protected cell editing is denied

In such cases, we need first to unlock the sheet and then go for the merging option.

There are many other problems that will not allow merging the cells.

They are:

  •  When the cells have data in the form of image
  • When the entered data is massive, merging and aligning becomes a significant issue.

Merge data in excel

Combining the cells using & symbol

  • First, you need to select the cell in which you want to put the combined data
  • Now type = and select the first cell that you wish to combine.
  • Now & with the help of quotation marks enclosing space
  •  Now select the next cell you wish to combine and click enter.

Combining using the concatenate function

  • Here you have to select a cell where you wish to put the combined data.
  • Now type = CONTACT(
  • Now you need to select the second cell which you wish to combine by using quotation marks, spaces, commas, or other text.

There are some problems in which merging becomes an issue.

We need to take care of the problems accordingly and later merge them.

You can merge cells horizontally and vertically into a single cell.

Aligning is the crucial point here, and do not forget to get the data to the center.


Tags


You may also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}