Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Replace text using a regular expression

Topic Labels: Formulas
638 4
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi!
I’m new to regular expressions and I want to create a formula in Airtable which switches the text left from the ‘-’ to the right and visa versa. So U27Y11/12-U13Y07/08 needs to become U13Y07/08-U27Y11/12 Basically swapping the right and left side. (the ‘-’ is always the separator)
Can you guys help me out?

4 Replies 4

Welcome to the community!

You don’t need REGEX for this, although I’m 100% sure that it can be done (and there are a couple of REGEX aficionados here in the community).

But you can simply use the FIND function to get the position of the dash, and then use the LEFT and RIGHT functions based on that number (and you’d also use the LEN function to figure out the length of the entire string).

However, before trying to assemble a formula that does all of that, it gets signifucantly easier if there will always be the exact same number of characters before the dash, and if there will always be the exact same number of characters after the dash.

Is this the case? If so, then you can just use the LEFT and RIGHT functions, without worrying about the position of the dash.

Thanks for your help Scott!
In 99% of the cases the amount of characters will be exactly the same as in my example. U27Y11/12-U13Y07/08 needs to become U13Y07/08-U27Y11/12
I can even make a separate column for the remaining 1% which will have only 1 character more on each side.

For those 99% of cases, the formula would be a very simple one:

RIGHT({Your Field Name},9) & "-" & LEFT({Your Field Name},9)

In the 1% of cases where there is one more character on each side, then you would use this formula:

RIGHT({Your Field Name},10) & "-" & LEFT({Your Field Name},10)

And you can combine them both into a master formula to evaluate which one to use, based on the length of the text string:

IF(
LEN({Your Field Name})=21,
RIGHT({Your Field Name},10) & "-" & LEFT({Your Field Name},10),
RIGHT({Your Field Name},9) & "-" & LEFT({Your Field Name},9)
)

It works like a charm Scott! Thank you!
Going to experiment with this powerful functionality as well, it inspired me to do some more things.