10

I have a Google Forms with a question that contains a drop-down box with the following values:

 - Fewer than 10
 - 11-25
 - 26-50
 - More than 50

If the user selects 11-25, Google Sheets renders the response data like this: ono halp

Under the formats dropdown in the toolbar, selecting "Plain Text" has the cell render as this date. Selecting "Normal" gives just 40,872, and selecting any other type of format gives $ 40,872.00, 980928:00:00, and other various numbers that aren't anything like 11-25, depending on what option I choose.

The workaround is to change the Forms response option to 11-25 apples, or to recognize when manipulating my data that '11/25/2011' really means '11-25 apples'.

But is there a way to get Google Sheets to stop turning 11-25 into a date?

9

You have to format the column in spreadsheet mode before any data is entered.

Here we have a form about waffles. The cells have been formatted before sending it to the recipients.

How many waffles would you like

And here is the result after a user fills out the result.

A lot of waffles

  • 1
    Aha. Shame there's no way to fix it after the fact, but, now I know! Thanks. – hairboat Aug 16 '11 at 19:57
  • 1
    This makes me wish Google would go out of business tomorrow. I now and forever hate Google, with a passion. This is the worst thing ever. I hate Google for this, DAMN YOU GOOGLE! – WraithKenny Mar 23 '15 at 5:03
  • 1
    @WraithKenny fight the power, Kenny. – mattsven Aug 4 '15 at 23:18
  • If you do Text to Columns conversion, then this won't work. – GeneCode Jan 29 at 3:38
6

You can also put 3 - 5 instead of 3-5. It worked for me.

  • That's a good workaround for people like me who forget to format the columns ahead of time. Thanks! – hairboat Sep 25 '12 at 17:28
2

Similar to @Brandon's A, use 11—25 (em dash as separator) rather than 11-25 (hyphen/minus as separator)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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