You could try:
Substitute ( LeftWords (Substitute ( Saint John Residents::Individual ; "-" ; "xxxx" ) ; 1 ) ; "xxxx" ; "-" )
Bingo -- it worked perfectly! Not being a programmer, I don't understand what the "xxxx" means, so if you have a minute or two, I'd love a simple explanation of the whole calculation so that I could get a fuller understanding for the next time I run into a similar issue. Thanks again!
Sorbsbuster is very inventive.
A nested substitution - the inner one changed - to xxx (only if - existed), found the lastxxxname, and substituted it back.
I agree: Sorbsbuster is indeed inventive. Thank you for the explanation -- much appreciated!
That was what you asked for, but I think you will prefer something more robust. That will correctly handle:
My Left Foot's Day-Lewis, Daniel
But fail with his pornstar sister, Day-Lewxxxxd, Debbie
It will also fail with the Sound Of Music's von Trapp, Maria
If you can rely on the comma being where it is (as you seem to be for the first name), then it would be more reliable to use:
Left ( Saint John Residents::Individual ; Position ( Saint John Residents::Individual ; ","; 1 ; 1 ) - 1 )
If you prefer the format of your original calculation for could use (inside your If statement):
Let ( [
t =Saint John Residents::Individual ;
p = Position ( t ; "," ; 1 ; 1)]
; Left ( t ; p - 1 ))
(BTW: you seemed to have built in the flexibility in your IF statment to alter what the name seperator is (although you call it 'comma filter') but the in the rest of the statement you hard-code the comma, so removing the flexibility. I think it would be a better exploitation if you renamed the 'comma filter' field as 'FilterCharacter', and then made it, for example:
Let ( [t =Individual;L = Length (t) ;p = Position ( t ; FilterCharacter ; 1 ; 1)] ; Right ( t ; L - p ))