How To Merge Cells in Google Sheets Without Losing Data

Sometimes you need to combine data from multiple cells in Google Sheets, but the regular Merge option just keeps the top-left value and drops everything else.

That’s not ideal when you want to keep all your information in one place.

For example, maybe you want to join a first and last name, or combine a few values into a single line for a report. There are a few easy ways to do this without losing anything.

In the following dataset, we have the names of students in Column A, their cities in Column B, and the corresponding countries listed in Column C.

Our goal is to merge data from the columns A, B, and C into column D without losing any data.

Let’s use the CONCATENATE function to merge the student’s name, city, and country into one cell with separators.

Steps:

➤ Select the output cell (D2) and insert the following formula.

➤ After pressing the Enter button, use the Fill Handle tool to apply the formula down the column for the remaining rows.

In the following dataset, we have the names of students in Column A. The cities they have visited are listed across Columns B, C, and D.

Let’s use the JOIN function to combine all visited cities into a single cell for each student. We’ll separate the cities using commas.

Steps:

➤ Select the output cell (E2) and insert the following formula.

➤ After pressing the Enter button, use the Fill Handle tool to copy the formula down the column for the rest of the students.

In the following dataset, we have the names of students in Column A. Their visited cities are listed in Columns B, C, and D. Some students have visited fewer than three cities, so a few cells are blank.

Let’s use the TEXTJOIN function to combine all the visited cities into a single cell. This function skips over empty cells automatically. So, it’s pretty handy when you have messed up data.

Steps:

➤ Select the output cell (E2) and insert the following formula.

➤ After pressing the Enter button, use the Fill Handle to apply the formula to the rest of the rows.

In the following dataset, we have student names in Column A, their cities in Column B, and countries in Column C. We’ll use the Ampersand operator (&) to combine these values into a single line of text.

Let’s use the Ampersand to create a readable sentence that includes the name, city, and country of each student.

Steps:

➤ Select the output cell (D2) and insert the following formula.

➤ After pressing Enter, drag the Fill Handle to apply the formula to the rest of the rows.

In the following dataset, we have student names in Column A, their cities in Column B, and countries in Column C.

Now we’ll use the CHAR(10) function to add line breaks inside a single cell, so each piece of information appears on a separate line.

Let’s use the Ampersand symbol with CHAR(10) to combine name, city, and country in one cell—each on its own line.

Steps:

➤ Select the output cell (D2) and insert the following formula.

➤ After pressing Enter, make sure to enable “Wrap Text” for Column D so the line breaks are visible. Then, use the Fill Handle to apply the formula to the other rows.

If you’re tired of dragging formulas down every time you update your sheet, this one’s for you. Apps Script lets you run a bit of code that does the merging for you. It’s built right into Google Sheets, so you don’t need anything extra.

We’ll work with data in Columns A, B, and C—student name, city, and country. Our goal is to put all of that into Column D in this format:

Steps:

➤ Go to Extensions and open Apps Script.

➤ A new tab will open, and you will see this page

➤ Delete the default function if there’s one.
➤ It’ll ask you to name the project. You can type something simple like “MergeDataScript”. Giving it a name helps you find it later if you create more scripts in the same file. In our case, we named it “Sample Merging with Google App Script”

➤ Paste this code:

function mergeNameCityCountry() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getRange("A2:C13").getValues();
  for (var i = 0; i < data.length; i++) {
    var name = data[i][0];
    var city = data[i][1];
    var country = data[i][2];
    if (name && city && country) {
      var result = name + " - " + city + ", " + country;
      sheet.getRange(i + 2, 4).setValue(result);
    }
  }
}

➤ Save the script by clicking the small disk icon.

➤ Then press Run. It’ll ask for permission the first time—just go ahead and allow.

➤ You will see the success message

➤ Now go to your Google sheet and you will see the merged data in the Student Detail column (D). You don’t need to use the Fill Handle option because all the rows are already filled with data.

Frequently Asked Questions

Why does sorting stop working when I merge cells?

It’s just how Sheets handles it. If even one cell in the range is merged, the whole sort can fail or go weird. Try to leave your sorting areas untouched—no merging there.

Can I freeze merged rows or columns?

Not really. Google Sheets doesn’t handle that well. You might be able to do it in parts, but it won’t behave right.

What happens when I unmerge something?

Only the top-left part stays. Everything else just disappears. So, copy the data somewhere else.

Concluding Words

Merging can be handy, but only if it fits your goal. Pick the method that suits what you’re trying to do. In this article, we described 5 processes to merge cells in Google Sheets without losing data. We suggest that you learn all methods so that you can pick one that will help you save time and do the job efficiently. Last of all, always back up your data before merging. If anything goes wrong, you can always start over from that backup.