8

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

Example: bob@example.comexample.com

4

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
    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. – Wayne Werner Oct 21 '14 at 16:48
6

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.

  • 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. – Jacob Jan Tuinstra Oct 21 '14 at 10:38
  • Why is (.*) too generic? Don't you want everything after the @? – Holloway Oct 21 '14 at 11:15
  • @Trengot I was thinking of something in line of "@([A-Za-z0-9-.]+)") – Jacob Jan Tuinstra Oct 21 '14 at 11:45
  • @Trengot See stackoverflow.com/a/7111947/1536038 – Jacob Jan Tuinstra Oct 21 '14 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 '14 at 18:20
3

If your email address (bob@example.com) 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, privacy policy and cookie policy

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