Extract VAT rate from a string with REGEX

[Pakal0u]

Hi,

I've a table with 6 different string value and I would like to extract the VAT rate from these strings with REGEX.

Can you help me to do so?

Example: 

String Value 1: VAT normal rate 20% blah blah blah => Target value: 20%

String Value 2: VAT reduced rate 10% blah blah blah => Target Value:  10%

String value 3: VAT high reduced rate de 2,10% blah blah blah => Target Value:  2,10%

String value 4: VAT special rate of 8,50% blah blah blah => Target Value:  8,5%

String value 5: VAT reduced rate 5.50 % blah blah blah => Target Value:  5,5%

String value 6: Corsica VAT special rate of 8,00% blah blah blah => Target Value:  8%

In the string value the decimal separator can be a dot (String value 5) or a coma (String value 3, String value 4, String value 6) or there can be no decimal sperator (String Value 1, String Value 2).

Can you help me do so using REGEX?

Thanks in advance for your help.

Best regards 

[apathetichell]

This should work.

[phottovy]

@Pakal0u 

Here is what I used for my RegEx: 

(\d+[,.]*\d*[^\s]*%)

[Maskell_Rascal · Accepted solution]

Hi @Pakal0u 

We can use the % as an identifier in order to extract the numbers from the strings as you listed below. 

Once extracted, I'm using a formula tool to replace the comma with a decimal and divide by 100 to get the percentage. 

See attached workflow. 

If this solves your issue please mark answer as correct, if not let me know!

Thanks!

Phil

[apathetichell]

@Maskell_Rascal  I did the same (along with the obligatory tonumber() which you used. as well.).

@Pakal0u- you do want the final version to use a . as a decimal separator - not a comma - correct?

@phottovyalways a fan of the negative regex match!

[phottovy]

@apathetichell @Maskell_Rascal  I took the more literal approach and just extracted the string values. It's always cool to see the variety of solutions to the same problem!