Modifying Names to Usernames

[madrock]

Hello All,

here is my excel problem.

Smith, John
Doe, Jane
Smithss, Jack
Whitess, John
Blackss, Jane
Bluesss, Peter
Longlastname, Phillip

I dont know if it's possible to have these converted to usernames. The format is 6/1 (first 6 character of last name and 1st character of first name)

of course if there isnt 6 characters of the last name it would be whatever is there like DOEJ

Is this possible?

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe this

	A​	B​
1​	Name​	Result​
2​	Smith, John​	SmithJ​
3​	Doe, Jane​	DoeJ​
4​	Smithss, Jack​	SmithsJ​
5​	Whitess, John​	WhitesJ​
6​	Blackss, Jane​	BlacksJ​
7​	Bluesss, Peter​	BluessP​
8​	Longlastname, Phillip​	LonglaP​

Formula in B2 copied down
=LEFT(LEFT(A2,SEARCH(",",A2)-1),6)&LEFT(TRIM(MID(A2,SEARCH(",",A2)+1,LEN(A2))))

Hope this helps

M.

 

[madrock]

Marcelo,

You are a wizard!!! This is absolutely fantastic and it totally worked.

Thank you very very much!!

Peace
Phil

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[madrock]

Sorry.. I have a question.... where do I make the change to make it 6/2, 6/3 instead 6/1. If the usernames already exists I will need to make changes etc.

Thanks in advance!

Peace
Phil

 

[Marcelo Branco]

To check if the user already exists would make the formula very complicated (i think you would need VBA), so i suggest you use 6/3 to all names..

Something like
B2
=LEFT(LEFT(A2,SEARCH(",",A2)-1),6)&LEFT(TRIM(MID(A2,SEARCH(",",A2)+1,LEN(A2))),3)

M.

 

[madrock]

I didnt explain the scenario correctly, but that formula you provide is exactly what i am looking for. I have to provide three proposed usernames (6/1, 6/2, and 6/3). I currently have about 100 names to work on and searching AD for that username takes way too much time (manually typing it out etc). This kick-*** formula that you provided allows me to just copy paste into AD and make any changes accordingly.

I may have other scenarios for usernames and if it's ok I will post here to keep it all in one place.

Thanks again and seriously... you have saved me a tremendous amount of time and I appreciate it!

Peace
Phil.

 

[Marcelo Branco]

You are welcome.

By the way, to create three lists, 6/1, 6/2 and 6/3, change the 3 (in red) in the formula above accordingly.

M.