In this post we’ll look at how we can check that emails addresses are in the correct format, numbers are indeed numbers, and that website addresses (URLs) are in the correct format. We’ll start with the basic checking of these and then move on to how we can highlight these using conditional formatting.
Throughout this post, we’re going to use the information in the table below. Here we have information relating to some parents of children at the school. We have their email address, phone numbers and the website where they can access their child’s reports.
We wish to check that their email, phone number and website URL are in formats that can be used.
Example 1 – Checking email addresses
In column B, we have their email addresses (obviously fictitious ones).
To check to see if the format of these email addresses is ok, in column E we write the following function:
This checks that the contents of cell B2 is in fact an email address. If it is, it returns the word “TRUE”. If not, it returns the word “FALSE”. We then copy that formula down the rows B3 to B6.
As we can see, it correctly identified that there is a problem with the last two addresses. We can see that the fourth one is missing the @ symbol and the fifth one is missing something like .com, or .co.uk at the end.
Example 2 – Checking for numbers
This time we want to check that the phone numbers in column C, are numbers and are not text or contain characters that maybe the computer system can’t handle.
To do this we write the following function:
This time it looks at cell C2 and checks to see it’s a number. If it is a number it returns “TRUE” and if not, it returns “FALSE”.
As we can see it’s found lots of problems. In cell C3, the number has a dash. In cell C4 there are spaces between the numbers. In cell C6, it’s obviously got some letters in there.
In cell C5, this number was entered as text and not a number, as it has a single apostrophe before the number to allow the number to start with a zero.
All of these situations mean that the contents of those cells aren’t numbers, at least in the way Sheets sees them.
Example 3 – Checking website addresses (URLs)
This time we want to check that the URLs in column D are in the correct format.
We write this function in cell G2:
This checks to see that the URL in cell D2 is ok.
As we can see, the first two are ok, but the last three have problems. In cell D4, the URL is missing the .com (or similar). In cell D5, the .com has 2 ‘m’s. In cell D6, the backslash is a forward slash.
Note, that the URLs don’t need the http: part nor the www. part to be recognised as genuine URLs. Sheets also automatically highlights correct URLs as hyperlinks.
Example 4 – Displaying different text depending on whether it’s TRUE or FALSE
In the examples above, our functions were displaying either TRUE or FALSE, which fine for a quick check, but don’t really look that good on your sheet. In the next few example we’ll look at how we can improve that feedback.
Firstly, we can change the wording from TRUE and FALSE to anything we would like. For example, let’s report ‘OK’ if it comes back TRUE if it’s an email, and ‘Not OK’ if it’s not.
In column E, we write the following formula:
This wraps the ISEMAIL function in an IF function. It looks at the content of cell B2 and if it’s true, it displays ‘OK’ and if it’s false it displays ‘Not OK’. See my post on the IF function if this is new to you.
Then we do the same for the other functions:
Now in columns E to G, we can see which ones are OK and which ones aren’t. This makes it a little clearer to anyone looking at your sheet.
Example 5 – Adding conditional formatting
Now let’s add some colour to show which are OK and not OK more clearly. Let’s put the OKs in green and the Not OKs in red.
Select the cells you want to colour.
Right click and select Conditional formatting.
This opens the Conditional format rules sidebar. Click on where it says “Cell is not empty”.
Then select “Is equal to” from the options.
In the box type “OK”.
Then select the colour you want by clicking on the fill icon.
As we can see, the first rule is now set up and it’ll fill any cell in with green that is equal to OK.
Click on “Add new rule”. Then repeat the process above, this time selecting red and in the box typing in Not OK.
This is far more visual and effective, especially if you have a long list.
If you want more information on conditional formatting, see this post.
Example 6 – Using custom formula to add colour to cells with the data in it
So far we’ve reported what’s OK and not OK in different cells, but quite often the better way is to highlight the cells themselves that are OK or not OK. For example, first let’s highlight the emails that are OK in green.
Select the emails and right-click and select Conditional formatting as before.
This time, at the bottom of the options, select “Custom formula is”.
In the box, type the ISEMAIL function referring to the first cell in your selection. In this case, cell B2.
Note that this automatically, applies your formula to the whole range you selected.
As we can see it highlights those emails that are indeed emails, i.e. which from the formula return as true.
Example 7 – Using NOT in a custom formula to highlight what isn’t true
Highlighting which emails are correct is fine, but I usually find that you normally want to know where there are any problem ones, as one assumes that the majority will be ok. As you can see in the picture below, we can clearly see which cells need attention.
The process is exactly the same as the previous example, except we’re going to change the formula slightly. Start off with the emails.
Having selected “Custom formula is”, type the following formula in the box:
This wraps the ISEMAIL function in a NOT function. Effectively, what it’s doing is saying if the cell content ISN’T an email then apply the formatting. We then add a red fill.
As we can see it’s highlighted the problematic cells in red.
We can then do the same for the numbers and URLs, selecting each range at a time and entering the following formulas.
This is much clearer and without the need of extra columns.
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store