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);
}

Which script are you using? Would you mind posting the code to it as well? @Saku — ale13, Jun 09 '20 at 14:58
@ale13 alright I just edit my post but the script doesn’t change anything because when I change the merged cell (K13) to a normal cell, I don’t have the space. So I guess the script is not the problem — Saku, Jun 09 '20 at 16:16
When you set the value for the `K13` cell, you are using the `Devise` value. But your `Devise` variable is composed of `"Devise + text_with_space_after_it"`. Is this what you are referring to? If not, how do you merge cells? And which cells? @Saku — ale13, Jun 10 '20 at 09:46
No the Devise variable doesn’t have space after but only when I copy the value of K13 it add multiple spaces as I said. But in an another cell, there is no space after. K13 is merged as K13:M15 — Saku, Jun 10 '20 at 18:29
I tried to replicate what you mentioned but I was unable to do so. Do you have any screenshots of the actual behavior and how you would like it to look like? — ale13, Jun 11 '20 at 08:03
Screen #1 : https://prnt.sc/syhk20 this is the screen from merged cells that give this output `4/Hawk Joe Capisce 5/Boris Rick Mishka Klaus Batya 6/Autres + Epic 7/!NOTHING!________________` (« _ » represent space that StackOverflow is hiding) Screen #2 : https://prnt.sc/syhlg6 from a normal cell this give this output : `4/Hawk Joe Capisce 5/Boris Rick Mishka Klaus Batya 6/Autres + Epic 7/!NOTHING!` — Saku, Jun 12 '20 at 11:46
Do you this option [here](https://i.stack.imgur.com/wgMX5.png) checked on the merge cells? — ale13, Jun 12 '20 at 12:01
Yes it is checked but I just tried them all and it’s still the same problem with those three option — Saku, Jun 12 '20 at 20:03
Can you replicate the same behavior you have mentioned but with other cells from another sheet/spreadsheet? — ale13, Jun 15 '20 at 13:29
@ale13 Yes I just tried on another spreadsheet, I merged some cells and input some text and even if I just write some text directly from keyboard, it does the space thing when I copy - paste — Saku, Jun 15 '20 at 20:51

score 0 · Answer 1 · answered Jun 17 '20 at 14:29

0

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.

answered Jun 17 '20 at 14:29

ale13

5,679
3
10
25

But I don’t use line breaks, I select my merged cell and hit CTRL C, I don’t actually copy the text in it. But, if I copy the text in the formula area, it has no space after. Also when I double click in the cell, like to edit it, there’s no space left after, and also if I copy the text, there’s no space after either. In fact, it causes this only if I copy the cell and not actually the text – Saku Jun 18 '20 at 16:08
It seems then that the issue is related to your formatting as coying a cell in merged cells does not create any space after it either. – ale13 Jun 22 '20 at 07:00
Check here on a fresh new spreadsheet the same problem : https://imgur.com/a/2qN3G5E – Saku Jun 22 '20 at 13:03
Maybe in this situation you can report this to **Help**->**Report a problem** from the Sheets app. – ale13 Jun 29 '20 at 10:26

score 0 · Answer 2 · answered Mar 15 '21 at 14:58

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.

Google Sheet merged cells create space

2 Answers2

Reference