Regex Line Extraction

[DelData]

Hi there,

Can you help...I am trying to use RegEx in Alteryx useing key words to identify data i need to extract from a column.

The picture below shows what i want......I want to.....

1. Extract the Delivery Mode in Column B to produce Column C
2. Extract the Category in Column B to produce Column D

...I have used things like reg101 and it does look to work online but when i try regex in Alteryx column C extracts the Delivery Mode but also includes all text after delivery mode which i do not want.

...Column D is a more complicated since Category has additional line spaces and I only want the line after its ")"

Can anyone help me on this....attached workflow to update 😀

 

[Luke_C]

Hi @DelData 

Give these a shot:

Delivery Mode:

REGEX_Replace([Data], ".*Delivery Mode\s+([^\r\n]+).*", "$1")

• .* → Matches everything before "Delivery Mode".

• Delivery Mode\s+ → Finds "Delivery Mode" followed by one or more spaces.

• ([^\r\n]+) → Captures everything on that same line (i.e., the delivery mode, like "Standard Class").

• .* → Matches everything after the captured value (but doesn't include it in the result).

• "$1" → Returns only the captured part (i.e., "Standard Class").

Category:

REGEX_Replace([Data], ".*Category\s*\(.*\)\s*([^\r\n]+).*", "$1")

• .*Category\s* → Looks for "Category" and allows optional spaces after it.

• \(.*\) → Matches everything inside the parentheses (we don’t need this part).

• \s*([^\r\n]+) → Captures everything after the parentheses (i.e., the category name, like "Best in Class").

• .* → Matches everything after the captured value (but doesn't include it in the result).

• "$1" → Returns only the captured part (i.e., "Best in Class").

[Pilsner]

Hello @DelData 

I have used the following two regex statements to extract the information you're after.

Delivery Mode: 'Delivery Mode (.*?)\n' This looks for everything after the words delivery mode, before the next new line.
Category: 'Category \(.*?\)\s(.*?)\n' This looks for the word 'Category', then finds the set of brackets, and extracts the text, after the')' bracket, before a new line.

As I used two separate regex tools (both set with the parse output method) I had to join the data back together. 

I have attached the workflow I used below. Please let me know if you have any questions.

Regards - Pilsner

[DelData]

Perfect....this works exactly as i wanted......I need to practice RegEX more.......thank yuu for taking time out to help 👍

[DelData]

Perfect....another great solutions and works exactly as i wanted......I need to practice RegEX more.......thank you for taking time out to help 👍

[Luke_C]

Great to hear! Regex looks like an alien language at first, but definitely an invaluable skill to invest time to learning. 

[OllieClarke · Accepted solution]

Hi @DelData 

I see you've already got some answers, but if you want a one tool/function solution then this RegEx in parse mode should do it:

Delivery Mode (.+?)\n.*\) (.+?)\n

this will capture whatever is on the line after Delivery Mode, and then whatever follows the final closing bracket (on the same line)



Hope that helps,

Ollie

[DelData]

Thanks Ollie......this is another great example.....i can see benefits of knowing more on RegEx 😀