Use RegEx to pull out characters next to a special character

[finnotee]

Hi there,

I have a string of letters in a title for different email headings, such as:

"Hurry now and get 30% off the Winter Sale!"

"Swimwear now reduced to £20, buy now!"

I'm trying to pull out the values for the percentage discount and currency discount into a column. I could split on the % or £ but I wanted to try using a RegEx expression to parse the 3 characters to the left (percentage discount) or right (currency discount) of the special characters (% £ $ €).

Does anyone know what this expression might be or how I could go about making these two extra columns that show the value of the discount?

Thank you!

[MarqueeCrew]

What do you think of creating 2 variables? One for percent and one for amount?

If contains([field],"%") then regex_replace([field],".*([0-9]+)\%.*",'$1')
Else null()
Endif

If contains([field],"£") then regex_replace([field],".*£([0-9]+).*",'$1')
Else null()
Endif

Cheers,

Mark

[finnotee]

Thanks for the reply! Could you possibly go through the RegEx syntax in your expression? I'm still trying to get to grips with the syntax.

Thank you!

F

[MarqueeCrew · Accepted solution]

If contains([field],"%") then regex_replace([field],".*([0-9]+)\%.*",'$1')
Else null()
Endif

.* is any character for any number of occurrences

() parenthesis is a target group
[] brackets are a set of characters to find. Here I used 0-9 zero to nine. I could have said \d for a digit
+ is 1 or more occurrences
\% is a special character of %

For the amount, you could have [€£$] and modify the if logic plus the replace.

If contains([field],"$") or contains([field],"€") or contains([field],"£") then regex_replace([field],".*([€£$]0-9]+).*",'$1')
Else null()
Endif

You can use regex101.com to explain a regex statement.

.*([€£$]0-9]+).*

Cheers,

Mark

[finnotee]

Great, thank you! And thank you for the extra Regex link.

F