Calculation to move "A", "An" and "The" to the end of a field.
Hi there! I am looking for the calculation that will move words like "a" "an" or "the" from the beginning to the end of a field.
Try this one:
T = YourTextField ;
Fw = LeftWords ( T ; 1 ) ;
Rest = RightWords ( T ; WordCount ( T ) - 1 )
Fw = "a" or Fw = "an" or Fw = "the" ; Rest & ", " & Fw ;
Let ( [ Fw = LeftWords ( YourTable::yourTextField ) ;
Rest = RightWords ( YourTable ; WordCount ( YourTable::YourTextField )
If ( Fw = "a" or Fw = "an" or Fw = "the" ; Rest & ", " & Fw ; YourTable::yourTextField )
) // Let
If you want to enter text into a text field and want that same text field to reorder the words in this fashion, you can set up the above expression as an auto-enter calculation that uses Self in place of YourTable::yourTextField as long as you clear the "Do not replace existing value" check box.
Thanks Phil. I am trying to use the calculation you provided, but I must be doing something wrong. No matter what I do, I always get a "There are too few parameters in this function" error message.
In case I wasn't clear before, I have a text field that is company names. I want to make a calculation field that moves "the" from the beginning of the company name field to the end of the field, for sorting purposes.
I left out part of the leftwords function:
LeftWords ( yourtable::text ; 1 )
Thanks Raybaudi for correcting the omissions in my original suggestion.
Thank you! That worked perfectly! :)
Great stuff Phil and Raybaudi!!
Quick question to OP. I would imagine you should put this calculation in the auto enter tab of the company field and check the "Do not replace...." box, so you can account for and manually change the field (record).
Otherwise, what do you do if the Company name is called "A One Cleaners"? It will turn into "One Cleaners, A" & wont sort properly.
Retrieving data ...