Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Formula to move word

Topic Labels: Formulas
Solved
Jump to Solution
1568 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Liz_Baehr2
4 - Data Explorer
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
Kamille_Parks
16 - Uranus
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:
image

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
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:
image

Thank you!
Got it working!