Stop a '+' from generating a formula

Question

I'm having a bit of a struggle. I'm creating a spreadsheet which uses plus signs (+) regularly. I want a semi-permanent fix for +'s turning into addition formulae.

Answer 1

The easiest workaround is to enter an apostrophe ' as the first character, right before the +.

Another approach is to enter the contents as a string formula like ="+5 blah".

An initial plus sign is very much needed for some types of data, e.g.- international phone numbers, so it is unfortunate that even setting the format to plain text does not help here.

Answer 2

In your Google Sheets select the area and go to FORMAT -----> NUMBER -----> PLAIN TEXT. Now the formulas will not work anymore!

Answer 3

Do a Find & Replace for = with ' and check "Also search within formulas."

Answer 4

Just add a space before the + sign. Seemed to work for me

Answer 5

Depending on your application, the following solution could be useful:

Highlight the cell(s) that you would like to appear with a +. Go to the "123" Formatting, choose "More Formats," then go to "Custom Number Format..."

Type "+"@ into the Custom Format box. This specifies to add a + before the entry no matter if it is a positive, negative, zero, or text entry.

Now, just type in your plain information, and it will appear with a + before it. The problem is that when dealing with formulas, the data will still be shown without the positive sign.

I used https://support.google.com/docs/answer/56470?p=drive_custom_numbers&rd=1.

Answer 6

The way to do this is by adding an apostrophe (') before the + symbol, as written in Silver Ringvee's answer. However, you can automate it using Google Apps Script.

Here is a simple Apps-Script script I created to solve the problem. Whenever you edit a cell, it searches through the sheet, and places apostrophe (') before the text, i.e. you can simply add +hello, without worrying about adding ' in the beginning. The script will do i automatically for you.

Usage-

Open the sheet, go to Tools->Script editor. In the editor that opens, paste the following code. Then add triggers to the script so that the script runs automatically whenever you edit a cell.

Adding trigger -

In the editor go to Resources -> Current project's triggers. In the box that opens select Add a new trigger In column Run select main function, in Events select From spreadsheet, then select On edit for the last column.

Code -

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function letterToColumn(letter)
{
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  Logger.log(data)
  for (var i = 0; i < data.length; i++) {
   for (var j=0;j<data[i].length;j++)
     if(data[i][j]=="#NAME?") {
     var clm=columnToLetter(j+1);
       var rng=clm+String(i+1);
       var range = sheet.getRange(rng);
       var frml = range.getFormula();
       range.setValue("'+"+frml.substring(1));
       Logger.log("sahil#"+frml);
     }
    Logger.log(data[i][j]);
  }
}

Answer 7

In my case, I added a space before the +. This worked but I had to remember to trim the values when using them elsewhere. I later discovered that I could simply select the offending cells and go to Data > Trim Whitespace.