ISEMAIL, ISURL and ISDATE functions in Google Sheets

ISEMAIL, ISURL and ISDATE test whether a value has the form of an email address, a web address or a date.

Lists of contacts collected from forms always contain a few entries that cannot be right: an email address without an @, a website typed as a name. These three functions spot them. They belong to the IS family, which answers TRUE or FALSE, and they exist only in Google Sheets.

Syntax

=ISEMAIL(value)

=ISURL(value)

=ISDATE(value)

Argument	What it is
`value`	The value or cell to test.

Examples

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

=ISEMAIL(B2)
Result: TRUE
asha.verma@example.com has the form of an email address.
=ISEMAIL(A3)
Result: FALSE
A name is not an email address.
=ISEMAIL("asha@example")
Result: FALSE
No recognised ending after the dot, so it fails.
=ISURL("https://www.example.com")
Result: TRUE
A web address with its protocol.
=ISURL("www.example.com")
Result: TRUE
The protocol is not required.
=ISURL(C2)
Result: FALSE
A code is not a web address.
=IF(ISEMAIL(B2),"OK","Check email")
Result: OK
The usual use: a flag beside each row.
=COUNTIF(ARRAYFORMULA(ISEMAIL(B2:B5)),TRUE)
Result: 4
Counting how many cells in a range pass the test.

What each one checks

Function	TRUE when the value
ISEMAIL	has the form of an email address: a name, an @, and a domain with a recognised ending
ISURL	has the form of a web address
ISDATE	is a date, or text that Sheets can read as a date

Sheets also has the IS functions shared with Excel: ISBLANK, ISNUMBER, ISTEXT, ISERROR, ISNA and ISFORMULA.

Form, not existence

All three check shape only. ISEMAIL does not send anything and cannot know whether a mailbox exists. A mistyped but well-formed address, such as one with a letter missing from the name, passes. So does an address at a domain that nobody owns.

Use these functions to catch slips of the keyboard, not to confirm that a contact is real. The only proof that an email address works is a message that arrives.

ISEMAIL judges the ending of the domain against a list of known endings, so an unusual or very new ending may be reported as FALSE although it is valid. Treat FALSE as "look at this one", not as "wrong".

Flagging bad rows

Put the test in a helper column and filter for FALSE:

=IF(B2="", "", IF(ISEMAIL(B2), "", "Check"))

The first IF leaves rows without an address blank, so that only real problems are flagged.

For a whole column from one formula: =ARRAYFORMULA(IF(B2:B="", "", IF(ISEMAIL(B2:B), "", "Check"))).

Stopping bad entries at the door

Data validation can refuse them as they are typed. Select the column, open Data, Data validation, and choose one of the built-in rules "Text is valid email" or "Text is valid URL". Or choose "Custom formula is" with =ISEMAIL(B2). Set it to reject the input, or just to show a warning.

In a Google Form, the email question type and response validation do the same job before the answer ever reaches the sheet.

Colouring them

In Format, Conditional formatting, use "Custom formula is" with =AND(B2<>"", NOT(ISEMAIL(B2))) and a red fill. Bad addresses then stand out without a helper column.

Stray spaces

An address with a space before or after it fails the test, and fails in real use too. Clean the column with TRIM, or with Data, Data clean-up, Trim whitespace, before checking.

ISDATE

ISDATE is TRUE for a real date and for text that Sheets recognises as one. Which texts are recognised depends on the spreadsheet's locale, under File, Settings: "12/11/2026" is a valid date in any locale, but which is the day and which the month differs. For a strict test that a cell holds a real date value and not text, combine it with ISNUMBER: a true date is stored as a number.

A stricter pattern

When your own rule is narrower than "any email address", for example only addresses at your organisation's domain, use REGEXMATCH: =REGEXMATCH(B2, "(?i)^[a-z0-9._-]+@example\.com$").

Common mistakes

Reading TRUE as "this address exists".
Not trimming spaces first.
Testing a whole range without ARRAYFORMULA and getting a single answer.
Forgetting to skip empty rows, so that every blank is flagged.

In Excel

Excel has no ISEMAIL, ISURL or ISDATE. There, people test for an @ and a dot with SEARCH, which is cruder. These formulas show #NAME? in an Excel file.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.