Excel Formula

[Pumaedition]

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]

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))

[Pumaedition]

super job! That'll do nicely! cheers!

 

I have been playing, but couldn't get it quite right!

[philterx]

Using =CONCATENATE() can help as well.

 

Cheers,

 

Phil

[mac_shinobi]

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]

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]

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..