Ah, the fun of string manipulations! As is often the case with problems like this, there can be a few different solutions. Here's my approach:
First, define the whole name as FULL NAME
2nd edit: It occurs to me that a last name containing a space, like "St. James", will be a problem with this approach. I must move on at this point, though. Anyone else have a more complete solution?
Let us know how it works for you.
Edited to show formulas with CODE tags.
Names can be so much fun and Kruncher has rightly pointed out one of the problems with name string analysis. Sometimes you can allow for this with a pre-check along the lines of looking for "St ", "St. " or similar - perhaps any space in the first 3 characters of the field, though I do appreciate this may lead to alternative problems with, for example, names of Chinese or Vietnamese origin. You can then make a decision about accepting them as know, valid forms and applying a slightly modified LSPLIT formula or perhaps reporting them as exclusions from automatic processing or as subject for some form of secondary process or maybe even ask for operator input at run time. It would all depend on your needs and purpose for the process.
One thought I has was to do something along the following lines, though this would NOT deal with the issue above where a space exists in the middle of the family name.
Use LSPLIT to create 2 calculated fields. Field one would be intended for LAST NAME and Field 2 THE REST. Then use RSPLIT on THE REST to create 2 fields, the field for MIDDLE NAME at the first space from the right and then WHAT's LEFT. WHAT's LEFT must be the First Name(s). However this will also not work as expected IF the persons MIDDLE NAME should be 2 names!
Just for now I will forget the complication above - the simplest form of the formulae, other than creating temporary calculated fields for working with, would be very similar to Kruncher's
[font="courier"]RSPLIT((LSPIT(,2," ",2)),2, " ",2)[/font][/quote]I would be happy to make some suggestions for identifying exception names as discussed but it may be best to know if you have that sort of problem (and some examples!) before diving in to make the whole subject more complicated. Something along the lines of accounting for the data length of the original field already allocated to FIRST NAME and MIDDLE NAME plus any spaces that separate them and deducting that value from the total length of the original field and selecting what is left might satisfy the requirement. Just a slight extension of DK's original suggestion perhaps.
I hope this helps.