RegEx Parse formula needed when more than 1 numeric field of same length exists

[182129]

I have the following sample data that I'm trying to parse a specific number field out of, but the problem is sometimes there's a different number of the same length of 7 digits that I don't want. I tried parsing by the 2 dashes, but then realized some of the valid data only has 1 dash. Is there some way to take into account that I only want to use data that contains "Need ARLO Number" but not include that phrase in the results?

Field3	Desired Result
No_Cost_Center-Need_ARLO_Number--1036528-10474934_0_1689001_WW00	1036528
No_Cost_Center-Need_ARLO_Number--1036569-10474975_0_1689001_WW00	1036569
No_Cost_Center-Need_ARLO_Number-HDQ-1036687-10475093_0_1689001_WW00	1036687
No_Cost_Center-Need_ARLO_Number--1036687-10475093_0_1689001_WW00	1036687
No_Cost_Center_WW00-7409101	null
No_Cost_Center_WW00-7301181	null
No_Cost_Center_WW00-7409101--No_ARLO_Number	null
No_Cost_Center-Need_ARLO_Number--1036503-10474909_0_1689001_WW00	1036503

[ShankerV]

Hi @182129 

It can be achieved with the combination of Regex and Formula tool.

Many thanks

Shanker V

[ShankerV]

Hi @182129 

Please find the use case explained for your scenario.

Step 1: Input the data

Step 2: Regex 

 

Step 3:

 

 

Step 4: Select tool to remove the unwanted columns

Many thanks

Shanker V

[182129]

Now that I think about it, could it be put in the same Formula tool using a RegEx_Replace with an IF contains "Need_ARLO"?

[ShankerV]

Hi @182129 

As usual, there are n number of ways we can achieve the result with the help of Alteryx.

I shared one of the way to reach the solution.

Glad that I brought creativity to your solution to use Regex_Replace.

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @182129 

Please use the below formula. Hope it helps!!!!

IF Contains([Field3],"Need_ARLO_Number")
THEN Trim(REGEX_Replace([Field3], "(.+)(-\d{7})(.+)", "$2"),"-")
ELSE null()
ENDIF

 

Many thanks

Shanker V

[sparksun]

My solution purely using Regex tool：