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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Smith, John​

[/td][td]

SmithJ​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Doe, Jane​

[/td][td]

DoeJ​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Smithss, Jack​

[/td][td]

SmithsJ​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Whitess, John​

[/td][td]

WhitesJ​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Blackss, Jane​

[/td][td]

BlacksJ​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Bluesss, Peter​

[/td][td]

BluessP​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Longlastname, Phillip​

[/td][td]

LonglaP​

[/td][/tr]
[/table]

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.