New to VBA and Excel, need help splitting a name field and dumping extra middle names

Hi,

I've never really used Excel for anything before, but all of a sudden I find myself tasked with sorting a column containing names into it's component parts of Mr P J Smith, but things being things it's not that simple! Some names do not have 'Mr', some do not have any first names, some have more than three first names (or initials, they are the same thing as far as this problem is concerned). I can ONLY have Title, First Name, Second Name, Sirname, and it must be in that format so empty cells have to be inserted to fill any gaps up to Sirname. The whole thing needs to be exported as a comma deliminated CSV file. I got as far as TextToColumns and it kind of worked, but I couldn't control where it placed the results (it just ended up wiping over existing columns) or removing any extra middle names or initials. The columns it creates need to replace the existing column so that the rest of the spreadsheet (columns) come after it. Every time I attempted to run the VBA macro and it didn't work properly I had to reload the spreadsheet, which also wiped out my macro as it was saved to that spreadsheet (I did not want to save the botched spreadsheet data)

I suppose that I could equally work on this outside of Excel completely, using text manipulation and such, but again I have no experience of that and can only hazard a guess that it would involve some complicated pattern matching and loops etc.

If anyone can help me out with this problem (bearing in mind I have NO Excel or VBA experience at all, nor the time to sit down and learn VBA programming, although I have some university programming background from years back, mainly C) I would be eternally grateful!! :D

These spreadsheets / CSV files may have as many as 1,000+ rows so manually sorting them isn't really an option.

If this problem can be broken down into smaller parts it may help, so for the first part I'm having trouble with I'd like help in splitting the contents of the cell into separate columns, so the original cell may contain Mr P J Duncan, I'd like Mr, P, J, Duncan in their own cells across four columns, and the original column ovewritten. I attempted this using TextToColumns but I fear my lack of experience with Excel and VBA meant that I made basic newbie errors.

Please Login or Register  to view this content.

I tried to use the Range function with TextToColumns to tell it to place the results starting in column B (column A contains a reference number) but could not find a way to tell it to insert the new columns rather than overwrite existing cells.

I looked at the insert columns method but was unable to combine it with TextToColumns.

If we can get this part working then I feel we're half way there. Parsing the name cell down to Title, FirstName, SecondName, SirName could be done before or after this stage as a separate function.

Really appreciate any help you gurus can give me on this!

Please read the Forum rules and then use the edit Button to amend your question's tile by removing all unnecessary words like "New to VBA and Excel, need help". Titles like the one you have used make the forum search engine less efficient