Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have Google Forms where users enter information, some of which contains leading zeros (e.g. zip codes, ID numbers).

However, when this data is saved to the Google Sheet, it doesn't treat it as plain text and strips the zero. The form fields are text fields with no validation.

I have tried to format the range of cells containing these values as plain text, but it seems Sheets does not set the formatting as plain text unless there is already text in a cell. So I can format a column as plain text, submit the form, and the leading zero will still be stripped.

I tried writing an apps script to deal with this but there's no good way for me to deploy it to the users who would need it.

How can I get around this without having to instruct users to prepend a single quote to their form entry? This is such a small thing, but I can't find a good simple solution to what it seems must be a common problem. If I can't get this to work I may have to drop Google Forms/Sheets altogether.

share|improve this question
    
Welcome to Web Applications! Good question, I have edited it slightly to make it easier to read. Have you seen How can I get Google Docs to stop formatting a form response as a date? –  Vidar S. Ramdal Aug 11 at 20:16
1  
Yes, and as noted above, I have tried what the "accepted answer" suggested, but it's for an older version of sheets and that doesn't work anymore. –  res Aug 11 at 20:23

2 Answers 2

Part of my answer would be: this is a known bug (newly received form submissions do not respect the format of the columns that they are landing in, as they did in the old version of sheets).

Depending on what you are doing with the data afterwards, another option would be to reproduce the form submissions on another sheet, with the correct format. For example, if column E contained zip codes that should be 5 digits long and include leading zeros (if required), then you could use:

=ArrayFormula(TEXT('Form Responses'!E:E,"00000"))

on another sheet.

You would need to bring over all your other columns as well, though. Without getting too complicated, this can be reasonably easily achieved in the new version of Sheets, and format the column(s) as desired, with (eg):

=QUERY('Form Responses'!A:G,"select * format E '00000'",1)

share|improve this answer

A long winded way of saying "I don't know how" coupled with "seems may not be possible" (but a real shame):

The best (but feeble) I can manage is to expect input (and thus also output) one digit at a time using a Scale of 0 to 9 for each digit.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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