Splitting a contact name 1

[lissa1974]

Hi there I have a field for contact name, which i need to split logically into 2 fields, being first name and last name. Currently the name could be formatted like....

J P Murphy
John Murhpy
JP Murphy

The majority of records are split john Murphy, so I want to say anything to the left of the space into first name, anything after into the surname.

Any ideas on the best way of doing this??

using crystal 2008 from SQL db.

Thanks

 

[Madawc]

You can do this with split, allowing for the variations. For instance

if ubound(split({your.field},"")) = 2 then
split({your.field},":")[2]
else
if ubound(split({your.field},"")) = 3 then
split({your.field},":")[3]

Something similar for first name, elements 1 or 2. Maybe allow for cases where there was just one name, or none, Ubound will tell you.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

I believe Madawc meant:

if ubound(split({your.field}," ")) = 2 then
split({your.field}," ")[2]else
if ubound(split({your.field}," ")) = 3 then
split({your.field}," ")[3]

But if you just want to identify the last name, you could use:

stringvar array x := split({your.field}," ");
x[ubound(x)]

This assumes there are no extensions like "Jr." or "Esq."

To put the first name and initial into one field, you could use:

stringvar array x := split({your.field}," ");
if ubound(x) = 3 then
x[1]+" "+x[2] else
if ubound(x) = 2 then
x[1]

I have never seen a middle initial included in a last name field--much more usual to combine it with a first name.

-LB

 

[lissa1974]

That works like a dream LB, once again many thanks for your help.