Returning domains from email addresses

[Rachel]

We have a worksheet containing 900+ contact details,
amongst them email addresses. I need to extract the
domain from the email address and display it in a
separate cell - i.e (e-mail address removed), I need to display
just the domain.com.au separately.

Tried the following formulas wihtout much luck:
=RIGHT(Q3, FIND("@",Q3,2))
=RIGHT(Q8,10)

Is it possible to do this and if so, how?

 

[J.E. McGimpsey]

One way:

=MID(Q3,FIND("@",Q3)+1,255)

where 255 is just a large number to make sure you get the whole
domain.

 

[Rachel]

Thanks, that seems to do the trick. Now I have
=MID(Q4,FIND("@",Q4)+1,255)
but need to set the instances without an email address to
blank. Currently the formula is filled down and if there
is no email address in the adjacent cell then it displays
#VALUE.

Can I put an IF or other function in there to solve?

 

[J.E. McGimpsey]

One way:

=IF(ISERR(FIND("@",Q4)),"",MID(Q4,FIND("@",Q4)+1,255))

 

[Rachel]

thanks, but that got rid of the error, but also removed
the domain extraction too leaving a blank cell for the
entries with email adddresses....

 

[J.E. McGimpsey]

Hmm...works here...

Since the extraction worked before the error trap, are you sure you
copied the formula correctly (try copying and pasting).