Where cell is the reference to the cell containing the email to be validated.
This formula checks if the email contains a valid top level domain such as .com, .net etc. In the case of Gmail, it makes sure it is gmail.com instead of just gmail. Incorrect top level domains is a common mistake when typing email addresses.
3. Press Enter
Once you press Enter, Google Sheets will analyze the email and determine if its format is complete with the domain and the top-level domain. It will display Valid if it is the case, while it will display Invalid if it is not the case.
4. Apply to Other Cells in the Column
Since we know that the formula works well, we can now copy the formula to other cells in the column. There are three ways to do so:
Autofill: The Autofill function appears when you add a formula for the first time in a cell that is part of a column. Google Sheets makes a suggestion of the additional range where you may add the formula. Click the Check symbol if the range matches well with what you want.
Double-click the Lower-Right Corner of the Cell: When you select a cell, a highlight box will appear. The blue highlight box has a small circle on its lower-right corner. Double click it to cover the other rows in the column.
Click and Drag the Lower-Right Corner of the Cell: When you select a cell, a highlight box will appear. The blue highlight box has a small circle on its lower-right corner. Click and drag it to cover the other rows in the column.
Whatever the method you choose, you will be able to get the same result:
You can now validate the format of the email addresses in Google Sheets!
Google Sheets Email Validation
Email validation in google sheets is easy. Simply use our custom formula below:
Is followed instead of username@domain, for example.
Can It Check if the Email Address is Functional?
It is possible that the email address format is followed, but the domain itself and/or the username does not exist. For that, you ultimately have to send a test email and wait for a response to see if it is received by the recipient.
If you enjoyed this article, check out some of our other popular articles: