help with formula

[Laura Vanhoorelbeke]

Hi,

I have been working on a worksheet in Excel and I have placed all my Email
Addresses in my Column A. Now I want to have all the domain names in my
column B.
For example:
Cel A1: (e-mail address removed)
Cel B1: hotmail

Now my question is, how can I do this with only 1 formula?

Thank you for helping me.

 

[Bob Phillips]

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

and copy down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Slightly shorter

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

 

[David Biddulph]

Shorter, but what does it do if his address is (e-mail address removed)?

 

[Roger Govier]

Hi Laura

Assuming you have 3 or more empty columns to the right of column A, you can
do it without formulae.
Copy column A to column B
Mark column B>Data>Text to columns>Delimited>Delimiter @ >Finish
Mark column C>Data>Text to columns>Delimited>Delimiter . >Finish
Delete Columns B and D

 

[Gord Dibben]

Without any formulas...................

Copy the column to an adjacent column. Select that column.

Data>Text to columns>Delimited by other>enter @ symbol>Next>Column Data
Format>Do not Import(skip).


Gord Dibben MS Excel MVP

 

[Bob Phillips]

Mine handles that but still bales out on (e-mail address removed)
<g>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David Biddulph]

.... and then something similar with the dot.

 

[Laura Vanhoorelbeke]

Thank you for helping me. I understand now.

Laura Vanhoorelbeke

 

[Guest]

I think Microsoft should consider allowing more than one character ( say "@"
and ".") to be specified to carry out the parsing in one step instead of
making us do it in two steps once for removing "@" and another for "." .
Would any one who can influence them tell them ?

 

[David Biddulph]

In this particular case, you couldn't do it in one stage. You need to split
it at the @ first, and then divide the 2nd column at the dot. If you were
dividing by dots and by @ symbols at the same time, then (e-mail address removed)
would put hotmail in the second column, but (e-mail address removed)
would put the domain in the third column.

 

[Dave Peterson]

You don't have to do the data|text to columns multiple times.

You could select the range and change each of the delimiters (space, @, .,
comma, etc) to a single character. Then do data|text to columns using that
single character.

 

[Guest]

Mr.Biddulph,
Thank you. I got the point.

Balan

In this particular case, you couldn't do it in one stage. You need to split
it at the @ first, and then divide the 2nd column at the dot. If you were
dividing by dots and by @ symbols at the same time, then (e-mail address removed)
would put hotmail in the second column, but (e-mail address removed)
would put the domain in the third column.

 

[Guest]

Mr.Peterson,
Thank you very much. Using "Substitute" function, I could achieve what you
had suggested. It was a new learning for me. But the problem of getting the
desired portion in different columns ( if the text is in different forms like
"(e-mail address removed) " and "(e-mail address removed) ") as mentioned by
Mr.David Biddulph persists.

Balan