I have a library file of books (way too many books!). The titles were entered with "The" and "A" at the beginning of the Title field, if that was part of the book's title. I would like to move these words to the end of the field after a comma and space. 

"The Name of the Rose" ==> "Name of the Rose, The"

More specifically I would like to create a calculated field, e.g. "TitleSansThe" and use a calculation to populate this field from the Title field. Can someone please point me in the right direction. I am new to calculations (and most of FMPro for that matter).

Thanks in advance.

John

PS. I searched for previous posts on this topic, but was unable to filter the 230K answers.

Try this as calculation: 

Let ( [
	title = books::title 
];
     Case ( 
       Left ( title ; 4 ) = "the " ; Middle ( title ; 5 ; 9999) & ", The"  ; 
       Left ( title ; 2 ) = "a " ; Middle ( title ; 3 ; 9999) & ", A"  ; 
     title 
  )
)

 

I would do:

Let ( 
firstWord = LeftWords ( Title ; 1 )
;
If ( firstWord = "The" or firstWord = "A" or firstWord = "An" ;
RightWords ( Title ; WordCount ( Title ) - 1 ) & ", " & firstWord ;
Title )
)

 

Thank-you Ocean West and thank-you Comment. I appreciate your answers and your time. I will try them out today.

Have a great week!