Build AD format usernames in CSV from the Full Names

Srikanth Ganipisetty_3xgf81

How to split User Full Name to build User Name according to AD in Excel Import file?

Ex: Full Name: Srikanth Ganipisetty

User Name should look like: ganips ( 1st five letters from second word and add first letter from the first word ). 

I found one possible way in constructing my CSV import file. But looking for more options to do this task more easier with your expertise in Excel or Scripts? 

Thanks,

Srikanth Ganipisetty

Srikanth Ganipisetty_3xgf81

Hi, 

I used these steps for 2K users actually, but finding better recommendations here. 

Step - 1: In the username column C2, enter this formula to extract the last word from the Full Name and hit enter. Drag and Drop or double click on the + icon so this formula can be executed to the below rows as well. 

=IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

Ex: Srikanth Ganipisetty gives Ganipisetty as the result. 

Step - 2: After you get the second word completely as shown in the expected output above, now it's time to add the first letter from the first word. 

Ex:  So usually, CSV list is sorted in alphabetical order if not sort it before you do any step. 

• If all your data is in column A with header " Full Name " (beginning at row 1).
• In column C User Name, row 1, enter = A1& " the letter it has on the full name " 
• In my Example: Srikanth Ganipisetty, it is s (in lowercase)
• This will make cell C1 equal A1 with this letter got appended.
• Now select cell C1 and drag/double click on the +bar from the bottom right of cell down through all your rows (this copies the formula and uses the corresponding column A value).

Note: This will append all users below with s at first. Now, do the above step by changing the formula within that row if there's any change in the first letter. Ex: Michael Debory . If you click on the row, now the ordered M items will get appended with m. it should change into deborys --> deborym. 

• Select the newly appended data, copy it into notepad so you can get the text rather than copying the formula into the desired column, paste it and rename the header as desired. 

Please let me know if you want me to add screenshots for better understanding.

Thanking you, 

Srikanth Ganipisetty