Regex Special Criteria Extract

Question

I am trying to have 3 Columns built using Regex.  but currently I can only have 2 at  a time in one regex

as shown below

Spoiler

Currency
value & %

How Can I have all the 3 column together without having having the issue  of the  percentage column disappear when I add currency.

Spoiler

Second Issue --->   How can I prevent Regex from extracting anything that is not currency in currency column

alteryx comm.yxmd

GMG0241 · Answer

Hi Jake,

My approach makes 2 columns to ensure that 45% doesn't give null, and then I split the 2nd column by a space to create the 3rd column.

The Regex does the following:

(\d+%)|((?:\d+,)?\d+\.\d{2}\s.*?)\s

(\d+%) - 1st capture group is capturing the percentage

| - or

((?:\d+,)?\d+\.\d{2}\s.*?)\s - 2nd capture group, (?:\d+,)?\d+\.\d{2} has a non-capture group and the regex checks if the currency value has a comma in it, \s.*?)\s is matching everything in between the space after the currency and the next space that appears after that, so the 2nd capture group captures the currency value, a space, and the information after the space and before the next space.

jerry_Tomson · Answer

The image shows a Regular Expression (Regex) configuration for data extraction. The Regex pattern being used is:

matlabCopyEdit(\d+%)|(\d{1,3}(,\d{3})*(\.\d+)?)(\s+)(\w+)

Breakdown of the Regex Pattern:

* (\d+%) → Captures percentage values (e.g., "45%").

* (\d{1,3}(,\d{3})*(\.\d+)?) → Captures numeric values, allowing:

* Thousands separator (e.g., "10,216.00").

* Decimal values (e.g., "400.50").

* (\s+) → Captures whitespace between the number and the currency.

* (\w+) → Captures the currency (e.g., "USD", "Bcus").

Extracted Fields in the Output:

* % → Percentage values.

* value → The extracted numerical values.

* currency → The extracted currency text.

Observations from Output Table:

* The regex correctly extracts:

* "45%" from "Bcus - Susie1 Bcus Plank 45% Alllllal".

* "400.50 USD" from "Bcus - automobil Bcus, 400.50 USD Alllsali".

* "10,216.00 USD" from "Bcus - submit Bcas, 10,216.00 USD Adddad: dfkl...".

Possible Enhancements:

* Allow Spaces in Currency (if needed):

* Modify (\w+) to ([A-Za-z\s]+) to accommodate multi-word currencies like "Hong Kong Dollar".

* Handle Edge Cases Like Negative Values:

* Update numeric pattern to (-?\d{1,3}(,\d{3})*(\.\d+)?) to capture negative numbers.

flying008 · Answer

Hi, @jake8281

FYI.

1-  Assuming that your [Value] field column always ends with . 00
2- You must unchecked  'Case Insensitive.'

Spoiler

(?<=,\s)(\d+%)?(\d[\d,]*?.00)?\s([A-Z]{2,})?

******

If can help you get your want, please mark it as a solution and give a like or more share.