How can I get Google Sheets to treat my form-submitted strings as plain text and preserve leading zeros?

Question

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.

Answer 1

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)

Answer 2

I have just hit this problem, but unfortunately I don't know the number of digits I'm expecting so I don't know how many zeros may be missing. 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.

You could also change the regular expression to force users to enter a single quote at the start of their entry

Answer 3

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.

Answer 4

The proposed solutions here and on other threads work only for numbers with a fixed number of digits. Like 5 for the zip code considered above.

I have to keep the leading zero of Belgian phone numbers.
Belgian land lines have the structure 025382172 (9 digits)
While mobile phones have the structure 0475123456 (10 digits)

I thought I had found a workaround duplicating the data in a new tab. To do this, I use two separate arrays to batch load groups of columns.

A first array copies all columns until the phone number column G, not included. =ARRAYFORMULA('Form Responses'!A2:F302)
A second array copies all columns after the phone number column G until the last one. =ARRAYFORMULA('Form Responses'!H2:P302)

In the phone number column I have a simple formula to append a zero before the value copied: =("0"&'Form Responses'!G2)

... unfortunately, it works only in static mode.
In dynamic mode, when a new row comes in from a Google Form, the formula in the phone number column G is messed up, even if the arrays are not supposed to affect this column.

Actually the formula =("0"&'Form Responses'!G2) shifts to the next row that is empty =("0"&'Form Responses'!G3) leaving the cell with a frustrating value of 0.

Will pursue my investigations by returning to a 1 single array for all columns in one block, adding a duplicate phone number column on the right outside the array zone and simply enter the following formula : =("0"&G3)... curious whether it will work... If it does, I'll mask the "native" phone number column and show only the processed one (unfortunately not at the right place on the page).

Answer 5

This is an older post, but I would recommended the following...

In the result tab, A1:

=QUERY('Form Responses'!A:F)

In G1:

=ArrayFormula(IF(ROW('Form Responses'!G:G)=1, 'Form Responses'!G1, IF('Form Responses'!G:G="", "", "0"&'Form Responses'!G:G)))

In H1:

=QUERY('Form Responses'!H:P)

A similar approach can be added to modify whichever columns' data needs modification.

Answer 6

I solved my zip code problem that begins with a "0" this way... Highlight column, go to format tab, click on "number", "more formats" and then "custom number formats" Since my zip codes are only 5 digits I typed in "00000" and hit apply. and it worked!

Answer 7

I've been collecting telephone numbers on a google form and it has been stripping out the leading zero when I export the responses. So, I have a workaround. When I export as a csv file to excel, I right click on the column that has the telephone numbers in, Format Cells, Custom and enter in the Type box 00000 000000 then click on OK. So a number entered in google forms as 01234567890 presents itself in the csv file as 1234567890, but the formatting displays it as 01234 567890 (UK telephone number) which is just what I want. Hope this helps anyone looking for a solution!