19

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.

25

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.

  • Out of pure curiosity - what is the significance of the apostrophe ' character? Like is it a general escape character in google sheets? – emmdee Mar 27 at 21:45
4

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

  • Not sure why this has been downvoted, worked perfectly for me. – Dylan Stark Sep 12 '17 at 1:58
  • The only one worked solution to do it in bulk! You can also select required column or fields for replacement. – laimison Aug 30 at 15:25
3

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

  • 2
    +1 on this. There is a twist, though: You have to do this BEFORE pasting the offending text :) – Arnaud Le Blanc Jan 8 at 8:37
  • @ArnaudLeBlanc this does not work unfortunately; before or after adding the format option. – brooklynsweb Jul 21 at 2:18
1

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

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.

  • I would do that, but I also need to use - signs as well. – David Wheatley Aug 8 '16 at 7:51
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.