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

try

rightwords(name,1)

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

Hope this helps

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

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

        n = ValueCount ( words ) ] ; 

   GetValue ( words ; n )  )

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

Fitch your code worked perfectly. Thank you very much.

CMack

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

Better to have two fields to begin with.

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, 200818 yr by Guest