Formula to move word

[Liz_Baehr2]

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?

[Kamille_Parks · Accepted solution]

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:

[Liz_Baehr2]

Kamille_Parks:

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

Thank you!
Got it working!