48

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.

2

9 Answers 9

56

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.

3
  • 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, 2019 at 21:45
  • @emmdee it seems so. I can't find any documentation in a quick search, though.
    – Ky -
    Dec 18, 2019 at 20:45
  • 1
    @emmdee, if you try to write only plus sign in the text, the Google Sheets will give you a notification that you need to start the text with an apostrophe to avoid interpreting it as a formula
    – mistika
    Apr 18, 2022 at 12:28
8

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

4
  • 1
    Not sure why this has been downvoted, worked perfectly for me. Sep 12, 2017 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, 2019 at 15:25
  • Absolutely the best solution. Should be the top answer!
    – ciamej
    Sep 25, 2020 at 10:41
  • At first I found this answer really confusing, because it implies that you're going to enter a bunch of data and then fix it, but then I realized that some people would end up in this situation only because they imported a bunch of data at once. However, this the question doesn't mention importing data, and using this method when you're entering stuff one cell at a time means you would have a messed up sheet for a while. I suspect that is why it got downvoted.
    – cesoid
    Sep 27 at 3:18
8

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

3
  • 7
    +1 on this. There is a twist, though: You have to do this BEFORE pasting the offending text :) Jan 8, 2019 at 8:37
  • 4
    @ArnaudLeBlanc this does not work unfortunately; before or after adding the format option. Jul 21, 2019 at 2:18
  • 7
    does not work, neither before nor after Aug 7, 2020 at 20:14
5

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

2
  • I find this less offensive than having to escape a + with a single quote, '+
    – sage
    Apr 15, 2021 at 18:07
  • When you an apostrophe it doesn't get rendered, the space does
    – Aido
    Oct 25 at 6:21
2

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

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.

1
  • I would do that, but I also need to use - signs as well. Aug 8, 2016 at 7:51
1

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.

2
  • In my case there's no need for computation so I just an invisible character before it (). Pro tip: you can copy this character from the HTML editor of the site.
    – Tomergt45
    Mar 17, 2021 at 22:50
  • The 'Trim Whitespace' feature inserts ' characters. 🙄
    – sage
    Apr 15, 2021 at 18:09
0

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.

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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