Creating 3 Feilds Names

[fmrock]

I have a field that is a name field.

I want to split this into 3 fields.

The names look like "Doe, John M". Not all the names have a middle initial.

But I have need 3 formulas that I can put this in a report as seperate columns.

Any help would be great.

 

[briangriffin]

This assumes that there will always be a first name (in other words, there will always be at least two elements in the array):


Last Name Formula:

replace(Split({NameField}," ")[1],",","")

First Name Formula:

split({NameField}," ")[2]

Middle Initial Formula:

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

 

[fmrock]

Yea, unfortunatly we do have some bad data where they only have one name. I need those to just go into the last name, and first name can be blank.

 

[briangriffin]

Then the first name formula will have to test for nulls like the middle initial formula:

if ubound(split({NameField}," ")) = 1 then ""
else split({NameField}," ")[2]

 

[fmrock]

The other issue I have now is some people have a space in their last name like "Doe Ray, John M" but the way these formulas work, first name=Ray and Middle=John

 

[briangriffin]

Then try these:

LastName:

whileprintingrecords;
stringvar array x;
stringvar v_lastname;
x := split({NameField}," ");

if ubound(x) = 4 then v_lastname := x[1] + " " + x[2]
else if ubound(x) = 3 and len(replace(x[3],".","")) > 1 then v_lastname := x[1] + " " + x[2]
else  v_lastname := x[1];

v_lastname

First Name:

whileprintingrecords;
stringvar array x;
stringvar v_firstname;
x := split({Sheet1_.Name}," ");

if ubound(x) = 4 then v_firstname := x[3]
else if ubound(x) = 3 and len(replace(x[3],".","")) > 1 then v_firstname := x[3]
else v_firstname := x[2];

v_firstname

Middle:

whileprintingrecords;
stringvar array x;
stringvar v_middle;
x := split({Sheet1_.Name}," ");

if ubound(x) = 4 then v_middle := x[4]
else if ubound(x) = 3 and len(replace(x[3],".","")) > 1 then v_middle := ""
else if ubound(x) = 3 then v_middle := x[3]
else v_middle := "";

v_middle