Sort "Last, First Middle" name field

[chanman525]

I haev searched the threads and can not find a match to do what i need. If someone has a few minutes to help out it would be much appreciated!

I have a field [Name] that contains Last,First Middle

Examples:

[Name]
Edgin,Bill Gene
Sexton Jr.,James Kenneth

I need to seperate out to [First] and [Last] and disregard the middle name so it look slike this.

[First]
Bill
James
[Last]
Edgin
Sexton Jr

Like I stated above, i found a couple of left functions that worked on part but i could not figure it out to completely and accuratly seperate as needed. Thanks for your time.

 

[Remou]

If the comma is there in every case, maybe:
[tt]strName = "Sexton Jr.,James Kenneth"
LastName = Left(strName, InStr(strName, ",") - 1)
FirstName = Mid(strName, InStr(strName, ",") + 1, _
InStr(InStr(strName, ","), strName, " ") _
- InStr(strName, ","))[/tt]

 

[MajP]

Like Remou said, Maybe. This is a not a good design to have a field as described, and you are probably going to have some exceptions. Why not normalize your data and create three fields? Fields should be atomic. If your field is normalized, it would be very easy to show your data in whatever way you want.
John R. Smith
John Smith
J.R. Smith
Smith, John R.
Smith, John
Smith, J.R.
Etc. etc.

 

[chanman525]

MajP:
The problem is the data is generated via 3rd party software(exported to xls, imported as table) and i am trying to manipulate it for my use. One of those "quick i gotta have it yesterday or the world is doomed" requests. Once the data is changed and made user friendly i plan to keep it for future requests.

 

[chanman525]

Thanks Remou, that got the data where I needed it. There are still a few fields that I will need to hand correct but it definitly limited it to a few records vs 700+. Thanks for your time and expertise.