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.
David Wheatley asked Aug 7, 2016 at 20:12 David Wheatley David Wheatley 595 1 1 gold badge 5 5 silver badges 12 12 bronze badges Commented Aug 8, 2016 at 4:32 Commented Aug 8, 2016 at 4:38The 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.
9,983 30 30 gold badges 32 32 silver badges 50 50 bronze badges answered Aug 7, 2016 at 20:37 Silver Ringvee Silver Ringvee 676 6 6 silver badges 5 5 bronze badges@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
Commented Apr 18, 2022 at 12:28Do a Find & Replace for = with ' and check "Also search within formulas."
answered Jun 7, 2017 at 21:41 89 1 1 silver badge 1 1 bronze badgeIn your Google Sheets select the area and go to FORMAT -----> NUMBER -----> PLAIN TEXT. Now the formulas will not work anymore!
answered Aug 8, 2018 at 17:21 Vipul Bijutkar Vipul Bijutkar 87 1 1 silver badge 1 1 bronze badge +1 on this. There is a twist, though: You have to do this BEFORE pasting the offending text :) Commented Jan 8, 2019 at 8:37 @ArnaudLeBlanc this does not work unfortunately; before or after adding the format option. Commented Jul 21, 2019 at 2:18 does not work, neither before nor after Commented Aug 7, 2020 at 20:14Just add a space before the + sign. Seemed to work for me
answered Jul 17, 2018 at 23:48 49 1 1 silver badge 1 1 bronze badge I find this less offensive than having to escape a + with a single quote, '+ Commented Apr 15, 2021 at 18:07 When you an apostrophe it doesn't get rendered, the space does Commented Oct 25, 2023 at 6:21The 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.
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;jLogger.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.
9,983 30 30 gold badges 32 32 silver badges 50 50 bronze badges answered Aug 8, 2016 at 2:54 121 3 3 bronze badgesIn 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.
9,983 30 30 gold badges 32 32 silver badges 50 50 bronze badges answered Nov 21, 2019 at 10:41 milehighsi milehighsi 109 1 1 bronze badgeIn 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.
Commented Mar 17, 2021 at 22:50 The 'Trim Whitespace' feature inserts ' characters. 🙄 Commented Apr 15, 2021 at 18:09 This is very similar to a previous answer -> webapps.stackexchange.com/a/118868 Commented Feb 28 at 18:24Just 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 :-)