Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 06, 2022 05:55 AM
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?
Solved! Go to Solution.
May 06, 2022 11:22 AM
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:
May 06, 2022 11:22 AM
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:
May 06, 2022 11:55 AM
Thank you!
Got it working!