Separating last name

[cmack]

I am trying to parse the last name out of a full name field.

I am using

Right(FullName; Length(FullName) - Position(FullName; " "; 1; 1))

It mostly works but here is my trouble.

I have names such as

John Smith

John W. Smith

John Smith-Jones

John W. Smith-Jones

I seem to be able to account for some of the combinations but not all of them. The calculation in the DB returns

W. Smith-Jones

So I tried making the " " to "." but that only works if there is a Middle initial and it leaves a space before the last name.

Any help would be greatly appreciated.

Cmack

[The Big Bear]

try

rightwords(name,1)

I just tried it on John w. simth-jones and it return the last name "Jones"

Hope this helps

[Fitch]

I think what you'd want there is "Smith-Jones" rather than "Jones." So:

Let( [ words = Substitute ( name ; " " ; ¶ ) ; 

        n = ValueCount ( words ) ] ; 

   GetValue ( words ; n )  )

[cmack]

Thank you both and yes I would need the whole name Smith-Jones.

Fitch your code worked perfectly. Thank you very much.

CMack

[David Jondreau]

Parsing names can be tough. What happens to Mr. von Helsing and to Cher?

Better to have two fields to begin with.

[cmack]

Yes I agree that having 2 fields to begin with is optimal. I have to import the data from another file and that file has the names in one field and I have no control over the format.

Hopefully Cher will never work for my school and Mr. Von Helsing's skill would best be learned at a high school. :-)

Edited February 14, 2008 by Guest