IF-ELSE with Left(find)..

[NetAdmin_noviceinexcel]

Hi Chandoo/Everyone,


This is my first post or so called challenge that I have with Excel. I have an extraction of my Active Directory(AD) and I wanted to break my userPrinicipalName(Email id) in two columns.


e.g to Illustrate, The data is in two forms in a column, "John.Smith@domain.com" & "JSmith@domain.com"


What I am trying is to create two columns that separate the data and puts in two diffrent columns, with separation of "@domain.com".


So, John.Smith@domain.com will be John.Smith in to B Column

and J.Smith@domain.com will be J.Smith in to C Column.


I've been trying to use nested IF else with Left(A2,Find("@"A2)-1), but I get a sense I am not doing it right.


I will really appreciate if any one can guide me or suggest me how to proceed with this..


Thanks in Advance for your time.

 

[Sajan]

Hi,

I am not sure if I am understanding you completely, since you indicate some values going to column B and others to Column C.


To extract the text before the "@", you could use the following formula (assuming cell A1 has the first e-mail address)

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


To extract the text after the "@", you could use the following formula:

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


Cheers,

Sajan.

 

[NetAdmin_noviceinexcel]

Hi Sajan,


First of thanks for your reply!!!


I want something like this,


ColumnA | ColunmB | ColumnC

John.Smith@Domain.com | John.Smith |

Jsmith@domain.com | | Jsmith

Alice.Wonderland@domain.com | Alice.Wonderland |

Sdwarf@domain.com | | Sdwarf


So, if the user is having firstname.lastname@domain.com, it should go to columnB else to ColumnC.


I am trying find a simple way to do this, I am not sure if IF Stmts are the answer for it. Or if there is any better way of doing this.


What you have suggested, I have done it using Left(A2,Find("@"A2)-1). But will surely try your's.


Thanks Again..


Have a Nice weekend.

 

[Faseeh]

Hi,


For First name before "." use:

=LEFT(LEFT(A1,FIND("@",A1)-1),FIND(".",LEFT(A1,FIND("@",A1)-1),1)-1)


For Second name after "." use

=RIGHT(LEFT(A1,FIND("@",A1)-1),LEN(LEFT(A1,FIND("@",A1)-1))-FIND(".",LEFT(A1,FIND("@",A1)-1),1))

...but what if the username has used underscore instead of a "."?


Regards,

 

[Hui]

You could also select the data

Then use Data, Text to Columns

Use a Delimeter and use a @ character

to separate the names from the domains

 

[Xenophon]

Hi,


If I understand your problem correctly, you have a list of Active Directory User Principal Names (UPNs) and you want to separate them into two columns; the first column will contain a list of UPNs of the format FirstName.LastName@domainname.com, and the second column will contain a list of UPNs of any other format. The '@' sign and the domain name is to be omitted from both columns.


Try using this formula in column B:

=IF(ISERROR(SEARCH(".*@",A1)),"",MID(A1,1,FIND("@",A1)-1))


Try using this formula in column C:

=IF(ISERROR(SEARCH(".*@",A1)),MID(A1,1,FIND("@",A1)-1),"")


Good luck!