Splitting text in FileMaker

Question

This may be a pretty easy question but I'm having some trouble with it. I have some information in a text field that logs a UserID in the form of firstname.lastname . What I need for reporting purposes is to split that data into 2 new fields (created as FirstName and LastName respectively). I've tried replacing field contents with a calculation using LeftWords and RightWords but it unfortunately did not work: LeftWords(Attendance::UserID; 1) for first name and RightWords(Attendance::UserID; 1) for last name. What it gave me was the UserID again. I think what is throwing me off is the 'period' that separates the UserID. Any suggestions? I've looked up a number of ways of splitting text, but most have been splitting text separated by a space...

Answer 1

For first name:

Left ( Attendance::UserID ; Position(Attendance::UserID; "."; 1; 1)-1 )

For last name:

Right( Attendance::UserID; Length(Attendance::UserID) - Position(Attendance::UserID; "."; 1; 1) )