Trimming fields using Space Delimeter without numbers first

[wanzek]

I have one field that has First Name Middle Initial Last Name all together separated by spaces. However, not every field has a midddle initial. I have tried searching for a formula that will work however I am not able to get exactly what I need. My information is as follows:

In the {HRDP.Name} table:

Liz J Johnson
Emily Anderson
Zach T.S. Butler

What I need:
Johnson Liz
Anderson Emily
Butler Zach

 

[fisheromacse]

This should give you the 1st and the last items together.

//{@FindName}
stringvar FirstName := left({table.field},instr({table.field}," "));

stringvar LastName := split({table.field}," ")[Ubouknd(split({table.field," "))];

FirstName &" "& LastName

 

[fisheromacse]

doh!
just noticed a type in the lastname

stringvar LastName := split({table.field}," ")[Ubouknd(split({table.field," "))];

should be: stringvar LastName := split({table.field}," ")[Ubound(split({table.field," "))];

i had a 'k' in the middle of 'ubound'

 

[wanzek]

Using this formula

stringvar FirstName := left({HRDP.Name},instr({HRDP.Name}," "));

stringvar LastName := split({HRDP.Name}," ")[Ubound(split({HRDP.Name}," "))];

FirstName &" "& LastName

I get error message: "A subscript must be between 1 and the size of the array.

 

[fisheromacse]

I made another typo....
stringvar FirstName := left({table.field},instr({table.field}," "));
should have been:
stringvar FirstName := left({table.field},(instr({table.field}," ")-1));


for your error, add a null check.

stringvar Firstname;
stringvar Lastname;

if isnull({HRDP.Name}) or trim({HRDP.Name})=""
then FirstName := ""
else
FirstName := left({table.field},(instr({table.field}," ")-1));

if isnull({HRDP.Name}) or trim({HRDP.Name})=""
then LastName := ""
else LastName := split({HRDP.Name}," ")[Ubound(split({HRDP.Name}," "))];

FirstName &" "& LastName

 

[wanzek]

I am using the following formula:

stringvar Firstname;
stringvar Lastname;

if isnull({HRDP.Name}) or trim({HRDP.Name})=""
then FirstName := ""
else
FirstName := left({HRDP.Name},(instr({HRDP.Name}," ")-1));

if isnull({HRDP.Name}) or trim({HRDP.Name})=""
then LastName := ""
else LastName := split({HRDP.Name}," ")[Ubound(split({HRDP.Name}," "))];

LastName&","&FirstName


This works great except some of the names are as follows:

Joan J Anderson II
Jonathon Anderson JR
Janet Smith

I need the following:
Anderson II,Joan J
Anderson Jr,Jonathon
Smith,Janet

Thank you