# Formula to move word

Topic Labels: Formulas
Solved
1157 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

I’m trying to workout a formula to identify a text field that starts with “The”, or “A”, and move the “The” or “A” to the end of the text string with a comma. So if the field said The Blue Book, I want it to say Blue Book, The
Or at the very least I want to delete “The” or “A” from the start of the text string, but not from Elsewhere in the string.
Can anyone point me in the right direction?

1 Solution

Accepted Solutions
16 - Uranus

Yes, actually. Using REGEX you can do this with a relatively simple formula:

``````TRIM(REGEX_REPLACE({Text Field}, "(^A |The )(.*)", "\$2, \$1"))
``````

The above formula moves "A " or "The " at the start of the string and moves it to the end preceded by a comma. The TRIM function just gets rid of any extra spaces at the start or end of the resulting string.

The formula won’t move “A” or “The” to the end if its not followed by a space (i.e. its part of a word). See examples below:

2 Replies 2
16 - Uranus

Yes, actually. Using REGEX you can do this with a relatively simple formula:

``````TRIM(REGEX_REPLACE({Text Field}, "(^A |The )(.*)", "\$2, \$1"))
``````

The above formula moves "A " or "The " at the start of the string and moves it to the end preceded by a comma. The TRIM function just gets rid of any extra spaces at the start or end of the resulting string.

The formula won’t move “A” or “The” to the end if its not followed by a space (i.e. its part of a word). See examples below:

4 - Data Explorer

Thank you!
Got it working!