first formula for validate

How to validate and sort e-mails with Google Sheets Mobile

Validating e-mails

If you have a list of e-mails you need to clean up; you can easily do it from your mobile device. This tutorial uses an iPad running iOS 8. You do not need to do any fancy coding, nor do you need to use a laptop computer. Google’s version of Sheets for the iPad lets you do this, albeit the sorting is slightly different than you may be used to. As you will see, you will have to sort the list with a function. The function will create a new, sorted list instead of sorting the existing one. It seems that the menu option to sort a list should exist, but as of early 2015, it is not in the simple menus.

First, let’s start with a list of e-mails. Some good and some not so good. An e-mail must have certain characteristics, including always having and @ sign, having no spaces, and having a .com or other domain type at the end. 

first formula for validate

To the right of the column containing the e-mails, is the ISEMAIL function in cell B1.  You can see the formula typed into the rectangle to the right of the blue function box.

ISEMAIL Syntax

=ISEMAIL(value)

where value in this example is a reference to where the e-mail resides. Note that this function is designed to work on one cell at a time and does not work for a range. Nor does it accept any more arguments. It only validates the email as good or not good and outputs the result in the cell, that’s all. 

Press the checkmark after you enter the function. You need to get the cell to look like it does below, with a blue box around it and a little green square on the underside of the box.

result of first validate formula with little green square one blue rectangle surrounding cell

This green square tells you you can copy to formula to the cells below by dragging it with your finger.

 

formula copied down

Sorting the list

After you have copied the function to the cells where you need it, you have validated all of the e-mails.  This may have been all you wanted to do or you may now want to sort the data by true and false to delete the non valid e-mails.  You no longer need to have the data highlighted. There is no menu option for sorting, so you must use a function. Choose an active cell at the bottom or to the right of the data to make room for the new list. As you may have guessed, the name of the function is SORT.

SORT Syntax

=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
  • range – location of the data to be sorted.
  • sort_column – number of the column within the range to use for the sort. Further, it can be a “`range` or a range outside of `range` containing the values by which to sort”. What that means is anyone’s guess, so let’s just ignore that one.
  • is_ascending – `TRUE` or `FALSE` – TRUE will sort in ascending order, and FALSE will be descending
  • sort_column2, is_ascending2, … [optional] – Can be used if you want to sort by secondary criteria in additional ranges (i.e. sort by ISEMAIL, then sort by e-mail address to end up with alphabetical lists of valid and non-valid e-mails)

sort result

There you are. You now have a sorted list. The next steps are up to you depending on how your plan to use the resultant list. Enjoy.

StaySorted Google Sheets Add-On

If you are sorting from the menus (instead of using the SORT function), you need to redo your sort every time you add a new row. That is unless you use the StaySorted add-on to automatically sort any new entries. Use it to help keep your spreadsheets organized.

Leave a Reply