3

I have a form that I'm using to collect responses on in Google Forms. One of the question includes a response that usually starts with a leading zero (like an account number).

When the response is collected in the spreadsheet, the leading zero is removed.

I've changed the format of that particular column to "Plain text", but it doesn't fix the issue for previous responses. Also, when I do this, new responses don't follow the formatting rule - it changes all new responses to "Automatic" formatting.

How can I resolve this?

marked as duplicate by Alex, Rubén, Vidar S. Ramdal, Community Jul 30 '15 at 15:38

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

I have just hit this problem, it's really annoying and there is no way I'm going to do it manually. Luckily I thought of this before I sent the form out, if you already have responses then this won't help (unless you send it out again).

To solve this problem I have:

  1. Set the question type to "Text"
  2. Under "Advanced Settings" I have selected "Regular Expression"
  3. In the next drop down box I have selected "Matches"
  4. In the text box I have entered ^[\d+]$

This regular expression forces the user to enter an open square bracket at the start of their response which then must be followed by at least one digit and it must be ended with a closed square bracket. For example [0021314] would be a valid entry. Note that this will only allow digits, if you just want to force the square brackets, you can use ^[.+]$ instead.

In the sheet, this will show as [0021314], so you will then need to remove the square brackets which can be done with a formula like =mid(A1,2,len(A1)-2) where A1 is the cell containing [0021314]. Auto fill and formatting the cells as plain text should do the rest of what you'll need.

  • I needed to escape [] in the regexp: ^\[\d+\]$ – dentarg Feb 13 '17 at 22:31
1

There is no way that the Form can resolve the issue for previously entered response - it has taken the input value and saved it as a number, meaning the leading values are lost, ie the sheet doesn't know if they were there or not. You will have to fix them manually.

What happens if you format the column in the responses spreadsheet as text? Do newly-added responses keep the right format after that

  • No, newly-added responses doesn't keep the right format. – dentarg Feb 13 '17 at 21:45
1

It would be very far from ideal, but the account number might be entered on a 0-9 Scale for each digit and then reassembled in the responses sheet with the likes of =C3&D3.

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