7 Replies Latest reply on Feb 26, 2015 7:46 AM by SteveMartino

# Calculation to move "A", "An" and "The" to the end of a field.

### Title

Calculation to move "A", "An" and "The" to the end of a field.

### Post

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.

Thanks!!

• ###### 1. Re: Calculation to move "A", "An" and "The" to the end of a field.

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.

• ###### 2. Re: Calculation to move "A", "An" and "The" to the end of a field.

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.

Thanks again!!

• ###### 3. Re: Calculation to move "A", "An" and "The" to the end of a field.

I left out part of the leftwords function:

LeftWords ( yourtable::text ; 1 )

• ###### 4. Re: Calculation to move "A", "An" and "The" to the end of a field.

Try this one:

Let([
T =  YourTextField ;
Fw = LeftWords ( T ; 1 ) ;
Rest = RightWords ( T ; WordCount ( T ) - 1 )
];
Case (
Fw = "a" or Fw = "an" or Fw = "the" ; Rest & ", " & Fw ;
T )
)

• ###### 5. Re: Calculation to move "A", "An" and "The" to the end of a field.

Thanks Raybaudi for correcting the omissions in my original suggestion.

• ###### 6. Re: Calculation to move "A", "An" and "The" to the end of a field.

Thank you! That worked perfectly! :)

• ###### 7. Re: Calculation to move "A", "An" and "The" to the end of a field.

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.