10

Which Google Spreadsheets formula do I use to get the domain part from an email address?

Example: [email protected]example.com

3 Answers 3

6

I would use the following formula to obtain an URL.

Formula

=IF(ISEMAIL(A2),
   REGEXEXTRACT(A2,"@(.+)"),
   IF(ISURL(A2),
     A2,
     IF(ISURL(REGEXEXTRACT(A2,"@(.+)")),
       REGEXEXTRACT(A2,"@(.+)"),
       "No valid entry"
     )
   )
 )

 Copy / Paste 
 =IF(ISEMAIL(A2),REGEXEXTRACT(A2,"@(.+)"),IF(ISURL(A2),A2,IF(ISURL(REGEXEXTRACT(A2,"@(.+)")),REGEXEXTRACT(A2,"@(.+)"),"No valid entry")))

Explained

A1 is validated for being an e-mail address. If TRUE, then use the REGEXEXTRACT formula you constructed. If the e-mail address turns out to be not a valid address, then validate for an URL. If the URL turns out to be not a valid URL, then check if the REGEX is an valid URL. Show text No valid entry is no of the IF statements are true.

Screenshot

enter image description here

Example

I've created an example file for you: Get domain from email address

1
  • 1
    If you want both (or don't mind having both), =split(A2, "@") in A3 would put bob in A3 and example.com in A4. A little more concise. Oct 21, 2014 at 16:48
10

This formula will do the job:

=REGEXEXTRACT(A1, "@(.*)")

If no @ is found, the original value is shown.

The =regexextract function (documentation) executes the regular expression parameter (@(.*)) against the value of cell A1, and returns the matching group.

7
  • 1
    I'm not an expert on REGEX (@Vidar is....), but the (.*) is too generic. Btw, if no match is found, it will throw an error. Oct 21, 2014 at 10:38
  • Why is (.*) too generic? Don't you want everything after the @?
    – Holloway
    Oct 21, 2014 at 11:15
  • @Trengot I was thinking of something in line of "@([A-Za-z0-9-.]+)") Oct 21, 2014 at 11:45
  • @Trengot See stackoverflow.com/a/7111947/1536038 Oct 21, 2014 at 11:46
  • 1
    @JacobJanTuinstra That regex won't work with all domain names, only ASCII ones. Domain names can have Unicode characters in them, e.g. 㯙㯜㯙㯟.net
    – user80142
    Oct 21, 2014 at 18:20
5

If your email address ([email protected]) is in A1

=index(split(A1,"@"),0,2) = example.com

=index(split(A1,"@"),0,1) = bob

You are splitting on the @ and than using index to select which column you want from the split.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.