Jump to content

Need calculation to move "The" or "A" at the front of a field to the end after a comma

Recommended Posts

  • Newbies

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.


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

Link to post
Share on other sites

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"  ; 


Link to post
Share on other sites

I would do:

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


  • Like 1
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.