[ms office - 2010] Seperating first and surname in Excel

[ryanplym]

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.

[pcstru]

The quotes are wrong in the find function. It needs to be

 

=FIND(".",A17)

 

I'm assuming the 12 is the year?

[ryanplym]

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.

[ryanplym]

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.

[mac_shinobi]

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

Edited July 23, 2012 by mac_shinobi

[ryanplym]

mac_shinobi - you are a genius. Thankyou so much. This is a huge help

[mac_shinobi]

mac_shinobi - you are a genius. Thankyou so much. This is a huge help

 

I wouldn't go that far, but you are welcome

[mac_shinobi]

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

[rdk]

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.