Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Formula to move word

Topic Labels: Formulas
Solved
Jump to Solution
342 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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!