Your problem is very similar to [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001616;p=1#000000"]this recent post[/url].
But because you're starting out with a different structure, one possible formula to generate the outcome you want is:
Left(FullName,Instr(",",FullName)-1) /font[/quote]where FullName is the name of your original field.
I thought that the NSplit() formula was perfect for this situation:
3 returns the Middle Initial
4 returns the Last Name
Apparently, the formula is not parsing the string correctly, so I came up with this formula:
[font="courier"]RSplit((LSplit(MyField,2,", ",2)),2," ",2) + " " + RSplit(MyField,2," ",1) + " " + LSplit(MyField,2,",",1) /font[/quote]The first embedded LSplit splits the string into two parts using the ", " (that's a comma AND space) returning the second part of the split. Then it RSplits that string on the first space from the right and returns the second string (left most string). This is in case the person has 2 "names" and an initial.
Then a space is appended + " ".
Then another RSplit is used similar to the first name, but returns the 1st part of the split which should be the middle initial.
Another space is appended.
The last part again LSplits the name on the "," and returns the 1st part which should be the Last Name.
I'm probably overlooking an easier solution, but this works.
EDIT: I see Data Kruncher came up with a different solution! Just goes to show you, there's always more than one way to solve a problem, thanks to the versatility of Monarch.