+ Post New Thread
Results 1 to 9 of 9
Office Software Thread, Seperating first and surname in Excel in Technical; ...
  1. #1

    Join Date
    May 2012
    Posts
    22
    Thank Post
    8
    Thanked 2 Times in 2 Posts
    Rep Power
    5

    Seperating first and surname in Excel

    Good morning all.
    I hope someone can help me. Over the summer holidays I need to add a first name and a surname of many students into our active directory. Rather than go though each student and manually type the names I thought I would create a excel document and use the DSMOD USER command although I have bumped into a slight problem. Does anyone know how to separate the username into a first and surname? For example:

    Username: 12james.smith

    I need the above to change to James Smith

    I have used the formula =LEFT(A17,FIND(”.“,A17)-1) but no joy.

    Thanks in advance.

  2. #2


    Join Date
    May 2009
    Posts
    3,002
    Thank Post
    265
    Thanked 795 Times in 602 Posts
    Rep Power
    289
    The quotes are wrong in the find function. It needs to be

    =FIND(".",A17)

    I'm assuming the 12 is the year?

  3. Thanks to pcstru from:

    ryanplym (23rd July 2012)

  4. #3

    Join Date
    May 2012
    Posts
    22
    Thank Post
    8
    Thanked 2 Times in 2 Posts
    Rep Power
    5
    Thanks pcstru,

    12 is the year of entry so I basically need to remove the year and just have the students username split into a first and surname.

    So far I have managed to remove the year using =REPLACE(A5,1,2," ") but no joy in replacing the full stop with a space.

    Thanks again for your help.

  5. #4

    Join Date
    May 2012
    Posts
    22
    Thank Post
    8
    Thanked 2 Times in 2 Posts
    Rep Power
    5
    I think I may have fixed it using a very long winded way round:

    To seprate the year I used: =REPLACE(A4,1,2,"")
    To remove the full stop, in the next cell I used: =SUBSTITUTE(E4,"."," ")
    Then to split the first name: =LEFT(G4,FIND(" ",G4)-1)
    and finally the surname: =RIGHT(G4,LEN(G4)-FIND(" ",G4))

    Now to concatenate. Wish me luck. Thanks for all your help.

  6. #5

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,756
    Thank Post
    3,265
    Thanked 1,052 Times in 973 Posts
    Rep Power
    365
    Quote Originally Posted by ryanplym View Post
    I think I may have fixed it using a very long winded way round:

    To seprate the year I used: =REPLACE(A4,1,2,"")
    To remove the full stop, in the next cell I used: =SUBSTITUTE(E4,"."," ")
    Then to split the first name: =LEFT(G4,FIND(" ",G4)-1)
    and finally the surname: =RIGHT(G4,LEN(G4)-FIND(" ",G4))

    Now to concatenate. Wish me luck. Thanks for all your help.
    A1 = 12james.smith

    =SUBSTITUTE(RIGHT(A1,LEN(A1)-2),"."," ")

    If you want the outputted forename surname to be Forename Surname then the below functions

    =PROPER(SUBSTITUTE(RIGHT(A1,LEN(A1)-2),"."," "))

    you don't require both - just so you can do this in one cell instead of using multiple cells
    Last edited by mac_shinobi; 23rd July 2012 at 11:37 AM.

  7. Thanks to mac_shinobi from:

    ryanplym (23rd July 2012)

  8. #6

    Join Date
    May 2012
    Posts
    22
    Thank Post
    8
    Thanked 2 Times in 2 Posts
    Rep Power
    5
    mac_shinobi - you are a genius. Thankyou so much. This is a huge help

  9. Thanks to ryanplym from:

    mac_shinobi (23rd July 2012)

  10. #7

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,756
    Thank Post
    3,265
    Thanked 1,052 Times in 973 Posts
    Rep Power
    365
    Quote Originally Posted by ryanplym View Post
    mac_shinobi - you are a genius. Thankyou so much. This is a huge help
    I wouldn't go that far, but you are welcome

  11. #8

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,756
    Thank Post
    3,265
    Thanked 1,052 Times in 973 Posts
    Rep Power
    365
    Just as some extra help it may be worth while popping over to WiseSoft - Resources for IT Professionals ( registration is free ) they have multiple utilities that are very helpful

    All Software : WiseSoft.co.uk Software

    Account Management Spreadsheet : Account Management Spreadsheet

    Bulk AD Users : Bulk AD Users

  12. #9
    rdk
    rdk is offline

    Join Date
    Sep 2008
    Posts
    137
    Thank Post
    2
    Thanked 20 Times in 19 Posts
    Rep Power
    33
    To separate james.smith into "james" & "smith" in separate columns:
    Highlight column
    Select data --> text to columns
    Choose "delimited" then next
    For the delimeter type in the fullstop
    Click next & finish and Robert is your mothers brother.

    The text to columns function is often very useful. You could also get rid of the "12" by first using text to columns fixed width, then separate the names as above.

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] SIMs reports and Macros in Excel
    By GillNash in forum MIS Systems
    Replies: 12
    Last Post: 6th December 2011, 12:00 PM
  2. [MS Office - 2003] First pesty macro in Excel. Trolling me hard.
    By El_Nombre in forum Office Software
    Replies: 7
    Last Post: 28th January 2011, 01:13 PM
  3. Header and footer in excel
    By ful56_uk in forum Office Software
    Replies: 2
    Last Post: 22nd January 2010, 09:38 PM
  4. need to extract text from a string in excel
    By projector1 in forum How do you do....it?
    Replies: 7
    Last Post: 14th February 2007, 01:41 PM
  5. Web query in Excel
    By pinemarten in forum How do you do....it?
    Replies: 2
    Last Post: 20th January 2006, 09:22 AM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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