extract last names__
I have a large spreadsheet that changes daily. All names of employees from all countries.Sometimes the names are formatted with commas but most of the time with spaces. The names could be from first and last name to first and 3 middle names then a last name, exampleJohn Paul JonesMohammed Jamison Phyllip AbdulJ. PhillipsI have tried the mid, right, and search functions together but have had no luck.Can someone give me a simple formula to extract only the last name on the right?Thank you for your help1 person needs an answerI do too
December 4th, 2010 9:25am

"Fran1ino" wrote in message news:Email removed for privacy...I have a large spreadsheet that changes daily. All names of employees from all countries.Sometimes the names are formatted with commas but most of the time with spaces. The names could be from first and last name to first and 3 middle names then a last name, exampleJohn Paul JonesMohammed Jamison Phyllip AbdulJ. PhillipsI have tried the mid, right, and search functions together but have had no luck.Can someone give me a simple formula to extract only the last name on the right?Thank you for your helpIt is better to use separate columns for the Forenames and Last Name fields. That would allow you to sort the spreadsheet alphabetically (on the Last Name field). It would also allow you to use the spreadsheet as a simple database (for mail merge, etc).Using the names in your example, the fields would become:Forenames Last NameJohn Paul JonesMohammed Jamison Phyllip AbdulJ PhillipsFor even greater flexibility, you could use extra fields, such as Salutation (Mr, Mrs, General, etc); First Initial; First name; Second Initial; Second Name; Third Initial; Third Name; and Last Name.Using the example of Mr Abdul, you would enter his details in the following fields:Salutation - Mr (or Sheik, etc)First Initial - MFirst Name - ohammedSecond Initial - JSecond Name - amisonThird Initial - PThird Name - hyllipLast Name - AbdulThat would allow you to use a mail merge utility to extract the data and print an alphabetical list of names - or a set of address labels.The permutations are virtually unlimited so depending upon your wishes, the first line of the address labels might read:John Paul Jones or Mr J P Jones or Mr John P JonesMohammed Jamison Phyllip Abdul or Mr M J P Abdul or Mr Mohammed J P Abdul.J Phillips or Mr J Phillips
Free Windows Admin Tool Kit Click here and download it now
December 4th, 2010 2:33pm

Appreciate the help, but the problem is that many different managers around the world enter the employee names into one database, from the database I download the file and open in excel; sorry i left out those details.When i open the spreadsheet, the names are all in one column, for 30,000 rows, as below.JOHN RALPH PHYLLIPjames, n spade thompsonmohamed, adbul abdul SmuggleIf I use the Text-to-Columns function, the last names are placed in many different columns; some names have 5 or 6 middle names, not a good solutioin.This is the script I put together but there has to be an easier way. If names are in column 1, I run this script in column2.Sub UserLastName()' Prints the last word in a name, when the name' is formatted 'first, middle, last' Dim fullName, middleName, LastName Dim space As Integer ' Full Name ActiveCell.SelectDo fullName = ActiveCell.Offset(0, -1).Value If fullName = "" Then End ' Get last name strings space = InStr(fullName, " ") LastName = Right(fullName, Len(fullName) - space) For a = 1 To 10 space = InStr(LastName, " ") LastName = Right(LastName, Len(LastName) - space) Next ActiveCell.Value = LastName ActiveCell.Offset(1, 0).SelectLoopEnd Sub
December 5th, 2010 9:45am

"Fran1ino" wrote in message news:Email removed for privacy...Appreciate the help, but the problem is that many different managers around the world enter the employee names into one database, from the database I download the file and open in excel; sorry i left out those details.When i open the spreadsheet, the names are all in one column, for 30,000 rows, as below.JOHN RALPH PHYLLIPjames, n spade thompsonmohamed, adbul abdul SmuggleIf I use the Text-to-Columns function, the last names are placed in many different columns; some names have 5 or 6 middle names, not a good solutioin.This is the script I put together but there has to be an easier way. If names are in column 1, I run this script in column2.Sub UserLastName()' Prints the last word in a name, when the name' is formatted 'first, middle, last' Dim fullName, middleName, LastName Dim space As Integer' Full Name ActiveCell.SelectDo fullName = ActiveCell.Offset(0, -1).Value If fullName = "" Then End' Get last name strings space = InStr(fullName, " ") LastName = Right(fullName, Len(fullName) - space) For a = 1 To 10 space = InStr(LastName, " ") LastName = Right(LastName, Len(LastName) - space) Next ActiveCell.Value = LastName ActiveCell.Offset(1, 0).SelectLoopEnd SubIn that case, I'm sorry, but I can't offer any other suggestions.The underlying database is flawed. Consequently, the best solution would be to persuade the owner of the database to add an additional "LastName" field.
Free Windows Admin Tool Kit Click here and download it now
December 5th, 2010 11:16am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics