+ Post New Thread
Results 1 to 7 of 7
How do you do....it? Thread, Excel Formula in Technical; Hi, I am shortly going to be importing all the new intake for September into the AD, and using a ...
  1. #1

    Join Date
    May 2007
    Location
    Norfolk
    Posts
    157
    Thank Post
    21
    Thanked 3 Times in 3 Posts
    Rep Power
    15

    Red face Excel Formula

    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!

  2. #2

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,794
    Thank Post
    517
    Thanked 2,468 Times in 1,912 Posts
    Blog Entries
    24
    Rep Power
    835
    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))

  3. Thanks to localzuk from:

    Pumaedition (6th July 2009)

  4. #3

    Join Date
    May 2007
    Location
    Norfolk
    Posts
    157
    Thank Post
    21
    Thanked 3 Times in 3 Posts
    Rep Power
    15
    super job! That'll do nicely! cheers!

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

  5. #4
    philterx's Avatar
    Join Date
    Jun 2009
    Location
    Gold Coast
    Posts
    14
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Blog Entries
    1
    Rep Power
    11
    Using =CONCATENATE() can help as well.

    Cheers,

    Phil

  6. #5

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,778
    Thank Post
    3,282
    Thanked 1,053 Times in 974 Posts
    Rep Power
    365
    Quote Originally Posted by localzuk View Post
    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 )

  7. #6


    Join Date
    Sep 2007
    Location
    UK
    Posts
    5,449
    Thank Post
    1,447
    Thanked 889 Times in 570 Posts
    Rep Power
    647
    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.
    Last edited by laserblazer; 8th July 2009 at 08:49 AM.

  8. #7

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,673
    Thank Post
    861
    Thanked 650 Times in 431 Posts
    Rep Power
    499
    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:
    Code:
    =LOWER(LEFT(A1,1)&(IF(B1<>"",B1&C1,C1)))
    Formula for displayName:
    Code:
    =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..

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2003] Excel formula?
    By leco in forum Office Software
    Replies: 3
    Last Post: 3rd July 2009, 10:43 AM
  2. [MS Office - 2003] Excel Nested IF formula
    By park_bench in forum Office Software
    Replies: 11
    Last Post: 8th May 2009, 04:24 PM
  3. Excel Formula
    By denon101 in forum How do you do....it?
    Replies: 3
    Last Post: 1st December 2008, 03:34 PM
  4. excel formula help again please
    By RabbieBurns in forum Windows
    Replies: 2
    Last Post: 13th August 2008, 05:31 PM
  5. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 04:56 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •