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? – phuclv Aug 8 '16 at 4:32
-
How to force excel to stop thinking your cell contains a formula – phuclv Aug 8 '16 at 4:38
Add a comment
|
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 '19 at 21:45 -
@emmdee it seems so. I can't find any documentation in a quick search, though. – Ben Leggiero Dec 18 '19 at 20:45
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 :) – Arnaud Le Blanc Jan 8 '19 at 8:37
-
@ArnaudLeBlanc this does not work unfortunately; before or after adding the format option. – brooklynsweb Jul 21 '19 at 2:18
-
1
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. – laimison Aug 30 '19 at 15:25
-
Just add a space before the + sign. Seemed to work for me
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]);
}
}
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.
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.
