Pumaedition Posted July 6, 2009 Posted July 6, 2009 Hi, I am shortly going to be importing all the new intake for September into the AD, and using a csv file to create the users. However, I need a formula to create our usernames! EG. Student called Joe Bloggs would have the username y9BloggsJ. So I need a formaula which will add the "y9" to the beginning of the surname, and then their first initial to the end. I know it can be done...I just can't remember the formula for it! Help please!
localzuk Posted July 6, 2009 Posted July 6, 2009 if you have 3 columns: y9 bloggs joe etc... then the following formula will stick em together and add the first char in: =A1&B1&(left(C1,1)) 1
Pumaedition Posted July 6, 2009 Author Posted July 6, 2009 super job! That'll do nicely! cheers! I have been playing, but couldn't get it quite right!
mac_shinobi Posted July 8, 2009 Posted July 8, 2009 if you have 3 columns: y9 bloggs joe etc... then the following formula will stick em together and add the first char in: =A1&B1&(left(C1,1)) If you could copy and paste a couple of rows from the csv and put that into a new csv and change the end user names / details etc and then upload that after to here we can see the output and amend that. If you do have column 1 with year9 or yr9 or something else then obviously that will need re formatting. Other then that the above should work fine ( and that's how I would of done it as well )
laserblazer Posted July 8, 2009 Posted July 8, 2009 (edited) The formatted names will be stored as a formula so you may want to copy that column to another one using Paste Special and selecting value. You can then delete the other columns that you don't need. I also use Excel to check for duplicates and correct them within Excel rather than having the import program decide how they will be differentiated. Edited July 8, 2009 by laserblazer
Gatt Posted July 8, 2009 Posted July 8, 2009 I use something similar that deals with a middle initial [Display] Name: Joe P Bloggs [Excel Cells A1, B1 & C1) username: jpbloggs Formula for username: =LOWER(LEFT(A1,1)&(IF(B1<>"",B1&C1,C1))) Formula for displayName: =A1&(IF(B1<>""," "&B1&" "&C1," "&C1)) The first formula converts the username to lowercase, uses the first letter of A1. It then checks to see if B1 contains any data, if it DOES then it includes the inital in B1 and the surname in C1, if B1 is empty, then it only displays the surname.. The second formula is very similar to the first, but inludes spaces around the initial or before the surname - depending on the value of B1 I use this in conjunction with a SIMS export and WiseSofts AccountManagement Spreadsheet..
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now