Hi, gary1987, and welcome to the Filemaker forum!
All you need to achieve the first is a consistent delimiter between the first name and the last name. If we can assume that they are always separated by a <space>, then what you want are the calculations:
For the first name
Position ( NameFull; " "; 1; 1) - 1
And for the last name
Length (NameFull ) - Position ( NameFull; " "; 1; 1)
If you want an explanation of what they are doing, just ask.
(Of course, if the names are not consistently in the format: 'FirstName <space> LastName' then you will have more fun. Like, with: 'Sarah Michelle Geller', or 'P J O'Rourke'.)
(If you can see the logic of the FirstName calculation, then the zipcode should be similar.)
Still new to FM. That said, why wouldn't you just use this:
LeftWords ( NameFull ; 1 )
RightWords ( NameFull ; 1 )
Of course this assumes NameFull is always composed of two words.
Why wouldn't you? Because I learned that way 15 years ago, always works for me, ain't broke, why fix it, and I really must get round to reading up on all these new-fangled functions the FM bods keep adding.
ralvy is dead right, of course.
I've got a database with exactly that challenge - a name field in which records show variously as "Fred Bloggs" and "Dr Fred Bloggs" (also variations such as Mr, Ms, and Mrs).
I need to get them into separate honorific, firstname, and lastname fields.
RightWords seems to do the lastname consistently.
But I'm stumped with the variations for the rest of it.
Grateful for your help.
Well, assuming you don't have any Middle Names, what about this:
Honorifc field: RightWords (NameField ; 3 )
FirstName field: RightWords (NameField ; 2 )
LastName field: RightWords (NameField ; 1 )
Unfortunately, that doesn't work - that honorific field returns the full content of the Name field (e.g. Dr Fred Bloggs), and the firstname field returns Fred Bloggs.
Part of the problem is that the data is not consistent - some records have just Fred Bloggs.
It needs to be able to cope with the variations.
Ah, yes. My error. Try this instead:
If ( WordCount ( NameField ) = 3 ; LeftWords ( NameField ; 1 ) ; "" )
LeftWords ( RightWords ( NameField ; 2 ) ; 1 )
RightWords ( NameField ; 1 )
Aha - magic!
I was thinking about the IF function but couldn't figure how to use it with XXXWords. Nesting RightWords within LeftWords is also an elegant solution.