Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,463,377
Community Members
 
Community Events
176
Community Groups

Build AD format usernames in CSV from the Full Names

Edited

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

1 comment

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

Comment

Log in or Sign up to comment