How to separate user name and domain name in email id?

H

Harshad

I have many mail ids in excell sheet in column A. I want to separate the
username and domain name in Column B and Column C respectively. e.g. Column
A: [email protected], Column B: abcde and Column C: gmail.com


thanks in advance

Harshad
 
M

mesuttasar

Harshad;233734 said:
I have many mail ids in excell sheet in column A. I want to separat
the
username and domain name in Column B and Column C respectively. e.g
Column
A: [email protected], Column B: abcde and Column C: gmail.com


thanks in advance

Harshad
Click to expand...

Hi this is my first post , please try that code.


Code
-------------------

Sub soyad_ayir()
Columns("B:C").ClearContents
For i = 1 To Cells(65536, 1).End(xlUp).Row
a = Split(Cells(i, 1), "@")
For j = 0 To UBound(a) - 1
Cells(i, 2) = Trim(Cells(i, 2) & " " & a(j))
Next j
Cells(i, 3) = Trim(a(UBound(a)))
Next i
End Sub
 
J

joeu2004

I have many mail ids in excell sheet in column A.
I want to separate the username and domain name in
Column B and Column C respectively. e.g. Column
A: [email protected], Column B: abcde and Column C:
gmail.com
Click to expand...

Several ways to do this. One way ....

If A1 is text of the form "username@domainname", then:

B1: =left(A1,find("@",A1)-1)

C1: =substitute(A1,B1&"@","")
 
P

Pecoflyer

Harshad;233734 said:
I have many mail ids in excell sheet in column A. I want to separat
the
username and domain name in Column B and Column C respectively. e.g
Column
A: [email protected], Column B: abcde and Column C: gmail.com


thanks in advance

Harshad
Click to expand...

Try Data-Text to columns - Check " delimited" enter @ as delimiter - O

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste
 
Top