Sheetgo Banner

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

Validating e-mails

If you have a list of e-mails that you need to clean up, you can easily do it from your mobile device. This tutorial was created using 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 a bit 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 nowhere to be found in the spartan menus that are offered.

First, let’s start with a list of e-mails. Some good and some not so good.  An e-mail must have certain charactersitics 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, the ISEMAIL function has been entered into cell B1.  You can see the formula typed into the rectangle to the right of the blue function box. The syntax of the function is

=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 is telling you that 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. As there is no menu option for sorting, you will have to use a function to accomplish this. Choose an active cell at the bottom or to the right of the data to be sorted to make room for the new list. As you may have guessed, the name of the function is SORT. The syntax is as follows.

=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 are planning to use the resultant list. Enjoy..