Is There An Easy Way To Allow A User To Enter + or - in a cell ?

[ajarmstron]

Hi - I had a spreadsheet which was designed using Excel, but I have an issue when it is converted into a Google Sheets. In one field there is a box where the user enters + or -. With my original Excel version I had a data validation list option where the user could select + or - or alternatively enter the character in via the keyboard. However, with Google Sheets this is very problematic as when the user selects + from the list or just enters + then Google Sheets immediately rejects this with "It looks like your formula has an error. If you don't want to enter a formula, begin your text with an apostrophe (')." I note there are no issues with getting the user to input a -.

I have tried changing the data validation list option so it contains two possible options '+ (an apostrophe at the front of the +) and -. However, Google Sheets does not accept the '+ input when it is chosen from the list!

I also tried having in my list 3 possible inputs '+, + and -. The addition of the + means that when the '+ is selected it is accepted because Google Sheets interprets this as a +, but the + option itself is rejected if chosen!

I have also tried changing the format option of the cell so it is plain text, however, it still does not work!

I would be grateful if anyone could explain how I can get my spreadsheet to work so the user just selects or enters + or - and the Google Sheets accepts this! Is this possible?

Thank you for your time in reading this!

[janmorris]

are you able to provide a link to a copy of the workbook with some sample data?

[ajarmstron]

Hi - I have made a working file, but this forum is not letting me post it because I need to post a few times!

[ajarmstron]

I am trying to post a link, but this forum will not let me. So despite spending some time making a file I cannot post it. I suppose I will have to try elsewhere.

[ajarmstron]

Sorry, this forum is still not letting me post my link. I hope by posting this it will let me do so in the future.

[janmorris]

try putting the link in a text file, and go to "Go Advanced", the select the file, then click "upload file", then close the window after it shows as attached.

[ajarmstron]

Where is Go Advanced?

[ajarmstron]

Hi - sorry I cannot see where Go Advanced is!

I have also tried to insert my link as an image!

[ajarmstron]

*h*t*t*p*s*:*/*/*d*o*c*s*.*g*o*o*g*l*e*.*c*o*m*/*s*p*r*e*a*d*s*h*e*e*t*s*/*d*/*1*D*g*a*u*C*M*x*i*u*C*x*B*a*t*o*8*v*i*2*E*d*M*v*s*l*Y*G*H*6*V*Z*E*P*w*i*H*u*p*U*R*I*1*s*/*e*d*i*t*?*u*s*p*=*s*h*a*r*i*n*g

Hi - I have tried inserting a * between all of the characters! I think this works

What you can see is I have tried to get it running in Google Sheets as much as possible. (It is an educational resource and I have left it with the correct answers showing in case people don't know the topic or
don't want to answer the questions!)

My thread is about cells G8 to G68 where you cannot type +, but '+

I also note the conditional formatting is "dodgy" as sometimes there are big blocks of cells which sometimes seem
to "switch off" and I would be grateful for any solutions!

[janmorris]

look at the image
also, there is a big yellow banner across the top of the forum, read it!

Screen Shot 2021-09-10 at 1.07.09 am.png

[ajarmstron]

Hi - thank you for continuing to post and hopefully you can now see a spreadsheet attached. I note there are no such buttons showing "Go Advanced"! This is the spreadsheet I was trying to work with in Google Sheets. It is the cells in column G and also M which I am asking about.

Thank you!
Alec Armstrong
However, the Go Advanced button appeared after posting this!

[janmorris]

i have tried a few different combinations of settings to force GS to treat the plus symbol purely as text, but it will not work.

after doing some further research on the error "It looks like your formula has an error. If you don't want to enter a formula, begin your text with an apostrophe", it seems the issue is that, like the equals sign, the plus symbol is a reserved character and automatically signifies a formula. this does not happen with a minus symbol because it is also used as a hyphen.

see here for a conversation regarding this exact issue:
https://support.google.com/docs/thre...-starting-with