Google Sheet merged cells create space

Question

I got a sheet with merged cells and in those merged cells, a script write its results.

When I copy this result in the merged cells, it gave me multiple spaces at the end. Like : Result #1________ (« _ » represent invisible space)

When I put the same result in a normal cell (not merged), it doesn’t put any space at the end. Result #1

I tried multiple cell format (Center aligned, left aligned, etc.) but nothing changed.

Do you have any idea why ?

Thanks !

EDIT : add script

Script

function Devise() {
  const sheetName = "Missions";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var Devise = "";
  var NombreMission = "";
  var NomOperateurs = "";

  if(sheet.getRange("H2").getValue()=="") { // Si la mission 7 est vide
    NombreMission = 6; // On compte seulement 6 missions
  } else {
    NombreMission = 7; // Sinon on compte 7 missions
  }


  for (i = 1; i < NombreMission+1; i++) {    // Boucle FOR pour NombreMission missions
    if(sheet.getRange(2,i+1).getValue()=="") { continue; } // Si la mission est vide, on la passe
    Devise = Devise + i + "/";
    l = 0;    // Variable pour indiquer "Rien" si personne à placer dans la mission
    NomOperateurs = ""; // Reset les noms pour la mission d'après

    for (j = 1; j < 27+1; j++) {   // Boucle FOR pour tous les opérateurs
      if(sheet.getRange(j+2,i+1).getFontWeight() == 'bold') {     // Vérifie si la case est en gras

        /*if(i!=NombreMission) {  // Tant qu'il ne s'agit pas de la dernière mission ...
          Devise = Devise + sheet.getRange(j+2,1).getValue() + " "; // ... on affiche les opérateurs
        }*/

        NomOperateurs = NomOperateurs + sheet.getRange(j+2,1).getValue() + " ";
        l = l + 1;  // On compte les opérateurs
      }
    }  // Fin Boucle FOR opérateurs

    if (l==24) {                              // S'il y a tous les operateurs sur une mission...
      Devise = Devise + "ALL OPs! "            // ... On affiche "All Op!"
    } else if (i==NombreMission && l!=0) {    // Sinon s'il s'agit de la dernière mission et qu'il reste des opérateurs à placer...
      Devise = Devise + "Autres + Epic ";     // ... On indique qu'il s'agit du reste et des épiques
    } else if (l==0) {                        // Sinon s'il n'y a aucun opérateurs à placer...
      Devise = Devise + "RIEN "               // ... On indique "RIEN"
    } else {                                  // Sinon ... 
      Devise = Devise + NomOperateurs;        // ... On affiche les opérateurs
    }



  }  // FIN BOUCLE FOR NombreMission
  if(NombreMission==6 && Devise!="") { Devise = Devise + "7/!NOTHING!";}
  sheet.getRange("K13").setValue(Devise);
}

Answer 1

Encountering the same issue. I have a merged cell with text. If I select the cell and paste it into notepad, It includes quite a lot of white space. I've checked and if the merged cell spans two rows, the white space includes a line break. If the merged cell spans one row but two columns, the white space does not include a line break.

If I have a single cell and have it take its value from the mered cell "=A1", the text does not include the white space.

So the addition of the whitespace is definitely the result of having a merged cell.

Answer 2

Your problem is related to the data you copied and the way that you copied it as pasting text in merged cells doesn't create any new lines.

Also, an important thing to keep in mind is that CTRL+ENTER creates the mentioned space also known as a line break.

So, for example, if this cell contains the text Text + line break:

And the text from the above cell is copied and pasted into a merged cell it will look like this - which is the same outcome as the one that you have mentioned:

But if you paste the same text to a simple cell, it will look like this:

This is essentially because the line break will signify the start of a new cell.

For example, this cell contains this text with line breaks:

After the text is copied and pasted into a different cell, this is how it will actually be pasted as:

In order to solve your issue, I suggest you to copy only the text needed and if possible to avoid using any line breaks.

Reference

• Edit and Format a Spreadsheet.