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

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.

Editor's Note: This code works on cells showing #NAME?

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 6

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 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.

Answer 8

Just found this thread when searching to solve a similar problem. And thought it was worth adding my solution in case it's of use to any passing souls who find this thread.

I was pleased to discover that I wasn't the only one to come to blows with this annoying 'feature' of Google Sheets (especially that formatting cells to Plain Text doesn't solve it).

In my case I am pasting in my bank account data. They recently changed credits (I get then sometimes ;-)) to show as "+ £123" (no quotes).

And I wanted to parse this to display £123. But as it was converting it to a formula it resulted in a #NAME? error.

My solution was to point the FORMULATEXT function at it. This displays the formula (that Sheets has helpfully created from the text pasted into it) as a text value, which I can then use the SUBSTITUTE function to clean out the offending "= +".

Unfortunately I then have the added problem that the vast majority of other cells I pasted in haven't been converted to formulas so these now resulted in the #N/A error.

So to solve this I tested the cell with the ISFORMULA function to determine whether to return the original value or the FORMULATEXT solution.

A massive kludge to solve an annoying problem! But I'm not trying to land a craft on Mars, just parse my online bank data for my own use...

Hope this might help someone :-)

Answer 9

I guess this is to get the phone numbers in E.164 format to be used in Twilio Google Sheet App. If yes, here is what happened.

What I tried was, put the numbers in column K without the '+' (18889991010), then in the "Phone number" column (which was column A) instead of numbers I put in ="+"&K2, which excel formatted to +18889991010. (Btw, the overall format for column A was Plain Text.

I tried sending the message once, didn't work, tried it again I was surprised it worked.

Here's my status message:

sent: Sat Jul 09 2022 08:35:48 GMT+0530 (India Standard Time)
sent: Sat Jul 09 2022 08:35:48 GMT+0530 (India Standard Time)
sent: Sat Jul 09 2022 08:35:48 GMT+0530 (India Standard Time)
error: Exception: Request failed for https://api.twilio.com returned code 400. Truncated server response: {"code": 21211, "message": "The 'To' number ++1888******8 is not a valid phone number.", "more_info": "https://www.twilio.com/docs/errors/21211", "... (use muteHttpExceptions option to examine full response).
sent: Sat Jul 09 2022 08:35:49 GMT+0530 (India Standard Time)

One number was in E.164 format already in column K, therefore came up with the error. Else worked fine.

I was surprised myself.

Answer 10

There is actually a quite easy solution.

Select the column that contains the + that turns into a formula.

On mac press [ ctrl + ` ]

On PC (Not 100% sure) press [ STRG + ` ]

It will still be a formula but wont display Error anymore. Now you can find and replace.

Press command + F to search (Alt + F on PC)

Select the affected column as custom range.

Enter what you want to be replaced under find

Enter a space under replace

Check the box "also check within formulas"

Press find all

Done