Phone numbers losing the leading zero in Google Form™ responses
Someone types 07700 900123 and the sheet shows 7700900123. Here is why it happens, how to get the original back, and how to stop it happening to the next response.
Why it happens
Forms stores exactly what was typed. The sheet is where things go wrong: answers are read the way a value you type into a cell is, so anything that looks like a number becomes one.
07700900123is read as the number 7,700,900,123. A number can't start with a zero, so the zero goes.+44 7700 900123starts with a plus, which Sheets reads as the start of a formula. The spaces make it an invalid formula, and the cell shows#ERROR!.07700 900123, with a space, usually survives as text. So one column can hold a mix of text and numbers, depending on how each person typed.
Getting the original back
The form still has what was typed. Open the form, go to Responses › Individual, and each answer is exactly as entered. For more than a handful, use the menu in the Responses tab to download the responses as a CSV. Open the file with the phone column set to plain text, and the leading zeros are there.
Guessing from the sheet is less safe. A 10-digit number starting with 7 is very likely a UK mobile with its zero missing, and you can add it back on your working tab:
=IF(AND(ISNUMBER(C2), LEN(C2) = 10, LEFT(C2, 1) = "7"), "0" & C2, C2)
But you can't tell from 7700900123 whether a zero was lost, or whether it was an
international number that never had one. Use the fix for numbers you know are UK mobiles, and
check anything else against the form.
Stop it for new responses
- Set the phone column to plain text on the responses tab: select the column, then Format › Number › Plain text. Do this before responses arrive. It changes how new answers are stored, not the ones already there.
-
Add response validation on the form. On a short-answer question choose
Response validation › Regular expression › Matches. For UK mobiles:
This rejects typos before they are submitted. It will also reject overseas numbers, so only use it if every respondent has a UK mobile.^(\+44\s?7\d{3}|07\d{3})\s?\d{3}\s?\d{3}$ - Say the format in the question, for example “Mobile number, e.g. 07700 900123”. Most people copy the example they are shown.
One format for the whole column
On your working tab, strip everything except digits and a leading plus, so every number takes the same form:
=REGEXREPLACE(TO_TEXT(C2), "[^\d+]", "")
TO_TEXT matters. Without it, a value that was stored as a number stays a number,
and the zero is lost again.
The one-click version
The add-on finds phone-like columns and writes them to the clean tab as text in one consistent format. The responses tab stays as it is. Where a number can't be repaired safely, it is listed rather than guessed.
Form Response Cleaner is a Google Sheets™ add-on: free for up to 250 responses, and your responses tab is never touched.
More on Google Form™ responses
- How to split Google Form™ checkbox responses into separate columns
- Count checkbox answers from a Google Form™ (COUNTIF on multi-select)
- Remove duplicate Google Form™ submissions, keeping the latest
- Google Form™ responses: shorten long question headers without breaking the form
- Why you shouldn't sort or edit the Form Responses tab (and what to do instead)
- The guide: every rule, with the by-hand formula
Google, Google Forms™, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Form Response Cleaner is not affiliated with or endorsed by Google.