Philippines Phone Number Validation: Use of REGEXMATCH in Google Sheets
Every school year thousands of students and teachers’ data were being added to the school database. As someone who was always given the task to narrow down lots of information after the school enrollment, validating students’ data manually is very exhausting.
Take this as an example, when you ask students to include the phone numbers of their emergency contact person, there are some instances wherein the declared numbers were invalid due to human error.
And the assigned personnel during enrollment sometimes overlooked this matter as they cater to other students’ concerns. With that, problems arise such as “how are you going to contact the students’ parents or guardians if there’s an emergency?”
But through REGEXMATCH, you can easily identify if the data is valid in one click, just as shown below.
USE CASE | EXAMPLE | REGEX \d{3}-\d{3}-\d{4} | REGEXMATCH () |
Phone Number Validation (Philippines) | 0917-731-411 | \d{4}-\d{3}-\d{4} | FALSE |
0917-731-4115 | \d{4}-\d{3}-\d{4} | TRUE |
The table above shows two examples of phone numbers; first the 10-digit number (0917-731-411) and the second one with 11-digit number (0917-731-4115).
Using the REGEXMATCH standard formula \d{3}-\d{3}-\d{4} and modifying its’ parameters aligned with the Philippine phone number format \d{4}-\d{3}-\d{4}, the given data was validated and was able to identify the correct and incorrect phone number format.
In particular, teachers conduct their exams using online platforms nowadays. Asking for students’ basic information such as full name or email address is necessary. Another example of data validation is given below.
USE CASE | EXAMPLE | REGEX | REGEXMATCH () |
Email Validation | jnlarb@gmailcom | \[\w\-]+(\.[\w\-]+)*@[\w\-]+(\.[\w\-]+)*(\.\w{2,3})+ | FALSE |
teach.jayca@gmail.com | \[\w\-]+(\.[\w\-]+)*@[\w\-]+(\.[\w\-]+)*(\.\w{2,3})+ | TRUE |
The first example is jnlarb@gmailcom has a missing character which is dot (.) in between gmail and com while the second one is teach.jayca@gmail.com.
Utilizing the REGEXMATCH formula for email validation \[\w\-]+(\.[\w\-]+)*@[\w\-]+(\.[\w\-]+)*(\.\w{2,3})+, the given data was validated thus identifying that the first example is incorrect due to missing character and the second one has the correct email address.
Aside from that, REGEXMATCH can be used for lower or uppercase detection. There are some students who enter their information in lower case even though the instruction is to enter in uppercase and it’s a common mistake.
USE CASE | EXAMPLE | REGEX | REGEXMATCH () |
Upper Case Detection | jones, karen | [A-Z]+ | FALSE |
JONES, KAREN | [A-Z]+ | TRUE |
Following the formula for upper case detection [A-Z]+ , the first example jones, karen was detected invalid and the second one, JONES, KAREN appears to be the correct data.
To sum it up, you may probably ask why use REGEXMATCH?
REGEXMATCH is created to match patterns using regular expressions in Google Sheets. It is a good tool to check errors, validate, clean, filter and classify your data to lessen the burden of manually correcting information. It will save time and make the work efficient and error free.