help with formula
 

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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

=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)

"Laura Vanhoorelbeke" wrote in message
...
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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

Slightly shorter

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


"Bob Phillips" wrote:

=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)

"Laura Vanhoorelbeke" wrote in message
...
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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

Shorter, but what does it do if his address is ?
--
David Biddulph

"Teethless mama" wrote in message
...
Slightly shorter

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


"Bob Phillips" wrote:

=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)

"Laura Vanhoorelbeke" wrote in message
...
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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

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 BDataText to columnsDelimitedDelimiter @ Finish
Mark column CDataText to columnsDelimitedDelimiter . Finish
Delete Columns B and D
--
Regards
Roger Govier



"Laura Vanhoorelbeke" wrote in message
...
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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

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

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

DataText to columnsDelimited by otherenter @ symbolNextColumn Data
FormatDo not Import(skip).


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 12:22:48 +0200, "Laura Vanhoorelbeke" wrote:

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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

Mine handles that but still bales out on
<g

--
HTH

Bob

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Shorter, but what does it do if his address is ?
--
David Biddulph

"Teethless mama" wrote in
message ...
Slightly shorter

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


"Bob Phillips" wrote:

=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)

"Laura Vanhoorelbeke" wrote in message
...
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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

.... and then something similar with the dot.
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Without any formulas...................

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

DataText to columnsDelimited by otherenter @ symbolNextColumn Data
FormatDo not Import(skip).


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 12:22:48 +0200, "Laura Vanhoorelbeke"
wrote:

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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

Thank you for helping me. I understand now.

Laura Vanhoorelbeke

help with formula
 

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" wrote:

.... and then something similar with the dot.
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Without any formulas...................

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

DataText to columnsDelimited by otherenter @ symbolNextColumn Data
FormatDo not Import(skip).


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 12:22:48 +0200, "Laura Vanhoorelbeke"
wrote:

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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

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
would put hotmail in the second column, but
would put the domain in the third column.
--
David Biddulph

"Balan" wrote in message
...

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" wrote:

.... and then something similar with the dot.
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Without any formulas...................

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

DataText to columnsDelimited by otherenter @ symbolNextColumn Data
FormatDo not Import(skip).


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 12:22:48 +0200, "Laura Vanhoorelbeke"
wrote:

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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

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.





Balan wrote:

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" wrote:

.... and then something similar with the dot.
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Without any formulas...................

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

DataText to columnsDelimited by otherenter @ symbolNextColumn Data
FormatDo not Import(skip).


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 12:22:48 +0200, "Laura Vanhoorelbeke"
wrote:

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:
Cel B1: hotmail

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

Thank you for helping me.






--

Dave Peterson

help with formula
 

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

Balan

"David Biddulph" wrote:

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
would put hotmail in the second column, but
would put the domain in the third column.
--
David Biddulph

"Balan" wrote in message
...

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" wrote:

.... and then something similar with the dot.
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Without any formulas...................

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

DataText to columnsDelimited by otherenter @ symbolNextColumn Data
FormatDo not Import(skip).


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 12:22:48 +0200, "Laura Vanhoorelbeke"
wrote:

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:
Cel B1: hotmail

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

Thank you for helping me.

help with formula
 

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
" and ") as mentioned by
Mr.David Biddulph persists.

Balan

"Dave Peterson" wrote:

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.





Balan wrote:

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" wrote:

.... and then something similar with the dot.
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Without any formulas...................

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

DataText to columnsDelimited by otherenter @ symbolNextColumn Data
FormatDo not Import(skip).


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 12:22:48 +0200, "Laura Vanhoorelbeke"
wrote:

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:
Cel B1: hotmail

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

Thank you for helping me.






--

Dave Peterson