Which Google Spreadsheets formula do I use to get the domain part from an email address?
Example: [email protected] → example.com
Add a comment
|
3 Answers
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
Example
I've created an example file for you: Get domain from email address
answered Oct 21, 2014 at 10:25
-
1If you want both (or don't mind having both),
=split(A2, "@")in A3 would putbobin A3 andexample.comin A4. A little more concise. Oct 21, 2014 at 16:48
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.
-
1I'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 -
-
@Trengot I was thinking of something in line of
"@([A-Za-z0-9-.]+)")Oct 21, 2014 at 11:45 -
-
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– user80142Oct 21, 2014 at 18:20
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.
