Split Name

[Blessy clara]

Hi Could someone help me to split names in access database



For Example

given_name coulmn

 Mark Brown
Mark L Brown
Mark Leo Brown
Mark L B Brown
Mark L De van
Mark L Brown, Jr
Brown, Mark

I want the names to be split into two columns as

given_name                 family_name

Mark L                                  Brown
Mark Leo                               Brown
Mark L B                               Brown
Mark L                                  De van ("De" to always be in family_name)
Mark L, Jr                              Brown   (, Jr to be always in given_name)
Mark                                     Brown

Thank you

[June7]

This is tricky because of the inconsistencies in name structure (middle name or initial, spaces in last name, the Jr or Sr or III suffixes).

Think will need to write a complex function using string manipulation to do this. Something like this: http://support.microsoft.com/kb/168799

[ajetrumpet]

oh hey...this is way off, but it's worth a mention anyway:

other languages have really good built in tools for parsing and things. For instance, PHP has so many functions that are useful, Gates should really take some lessons from it. But at any rate, the pt I'm making is that exporting this stuff to a 3rd party's software or another app where there IS a tool to do this might be worth more than trying to write a function, especially if you know nothing about vb, vba, or any other flavor of ms's bs.

good luck to ya!