Withdraw domain name from mail address

  • Thread starter Xavier Vanmeerhaeghe
  • Start date
X

Xavier Vanmeerhaeghe

Hi,

If i have a column with mail addresses.
Is it possible then to withdraw the domain name from the address to the
column next to it?
example: cell A1= [email protected], cell A2=
[email protected], and so on.
cell B1= domainname33, cell B2= domainname99
thanks in advance!

With kindest regards,
Xavier Vanmeerhaeghe
 
B

Bernie Deitrick

If the final . is always followed by three letters .com .org .edu etc...

=LEFT(MID(A1,FIND("@",A1)+1, LEN(A1)),LEN(MID(A1,FIND("@",A1)+1, LEN(A1)))-4)

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Gary''s Student,

That gives "somebody", not "domainname33"

HTH,
Bernie
MS Excel MVP
 
I

IanC

Hi Xavier

Owing to email addresses varying so much, the formula become a little
involved.
Potentially you could have an email address such as
[email protected] so you need to search for "@", then find the
first "." after it and extract the data between.

I think the formula below should do it

=MID(A1,FIND("@",A1)+1,FIND(".",A1,FIND("@",A1))-FIND("@",A1)-1)
 
B

Bernie Deitrick

Ian,

The address can also have subdomains:

[email protected]

and this.that would be the appropriate domain.... so that is another thing to check for.

But only Xavier can tell which is best.

HTH,
Bernie
MS Excel MVP
 
I

IanC

Bernie Deitrick said:
Ian,

The address can also have subdomains:

[email protected]
Click to expand...

Good point.
and this.that would be the appropriate domain.... so that is another thing
to check for.

But only Xavier can tell which is best.
Click to expand...

It may be that he needs both, but there may not be any way to determine
which he needs in any specific circumstance. It may be that everything that
ends in .com can be extracted using your method and everything else using my
method.

As you say, only Xavier can determine exactly what he needs.
 
X

Xavier Vanmeerhaeghe

IanC said:
Good point.

It may be that he needs both, but there may not be any way to determine
which he needs in any specific circumstance. It may be that everything
that ends in .com can be extracted using your method and everything else
using my method.

As you say, only Xavier can determine exactly what he needs.
Click to expand...

Hi,
Very kind of you guys to help me out.
But I have been trying these formula's out in Excel 2007 and none of them
seems to work here??
I have no idea why.

The domain name of the mail addresses mostly consist of 1 word.
This might help you guys out.

Thanks for helping me out!

Xavier Vanmeerhaeghe
 
B

Bernie Deitrick

Xavier,

What value do you have?
What cell is it in?
What is your exact formula?
What result are you seeing?
What is the format of the cell with the formula? (if it is text, the formula won't work, even though
you want to return a 'text' value, you should use General)

HTH,
Bernie
MS Excel MVP
 
R

Ron Rosenfeld

Hi,

If i have a column with mail addresses.
Is it possible then to withdraw the domain name from the address to the
column next to it?
example: cell A1= [email protected], cell A2=
[email protected], and so on.
cell B1= domainname33, cell B2= domainname99
thanks in advance!

With kindest regards,
Xavier Vanmeerhaeghe
Click to expand...

To return the word between the "@" and the first ".", you can use this:

=REPLACE(MID(A1,FIND("@",A1)+1,255),FIND(".",MID(A1,FIND("@",A1)+1,255)),255,"")

--ron
 
X

Xavier Vanmeerhaeghe

Hi guys,
I solved the problem. I am using a dutch version of office.
So i had to chance the formula's to the dutch variant.
Everything is working now.

Thans a lot!

greets,
xavier vanmeerhaeghe
 
Top