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.
-
1How to prevent Excel from inserting formulas when typing + or - at the beginning of a cell?– phuclvAug 8, 2016 at 4:32
-
How to force excel to stop thinking your cell contains a formula– phuclvAug 8, 2016 at 4:38
7 Answers
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?– emmdeeMar 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– mistikaApr 18 at 12:28
Do a Find & Replace for = with ' and check "Also search within formulas."
-
-
The only one worked solution to do it in bulk! You can also select required column or fields for replacement.– laimisonAug 30, 2019 at 15:25
-
In your Google Sheets select the area and go to FORMAT -----> NUMBER -----> PLAIN TEXT. Now the formulas will not work anymore!
-
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
-
3@ArnaudLeBlanc this does not work unfortunately; before or after adding the format option. Jul 21, 2019 at 2:18
-
6
Just add a space before the + sign. Seemed to work for me
-
I find this less offensive than having to escape a
+
with a single quote,'+
– sageApr 15, 2021 at 18:07
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.
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]);
}
}
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.
-
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. Mar 17, 2021 at 22:50 -