Please help on Alteryx - how to extract part of the string with given conditions

[JT0168]

Given strings "INV # 34571, P.O. # 0002015101" or sometimes "2024597-1" and wanted to get those character starting from either one of the following 201, 202, 203, 204, 205 including the next 4 characters after 201, 202, 203, 204, 205 thereby having in my new field the following strings 2045101 and 2024597.  Thanks!    

[IraWatt]

Hey @JT0168,

I would use the Regex Parse tool for this:

(20[12345].{4})

This regex gets 201, 202, 203, 204, 205 then 4 characters after it.

Any questions or issues please ask :)
HTH!
Ira

[PhilipMannering]

You can use the Regex Tool set to Parse and use the expression,

(20[1-5].{4})

[binuacs]

@JT0168 one way of doing this with the Regex Tokenize

[JT0168]

Thanks for the solution but I also need to include this.....

How about if there is another part in that same column that I need to extract those with "R0000" followed by 5 characters how to include that in the same expression? the actual string in the column would look the same 

 "INV # 34571, P.O. # R000031361" or sometimes "R000045241-1"

the output must be

R000031361

R000045241 

[PhilipMannering]

To prevent too much back and forth, can you supply the input with a decent sample and expected output? I'm sure between us we can solve this 

[IraWatt · Accepted solution]

Hey @JT0168 

For your R0000 I added a | which means OR in Regex, I then added R0000 so it looks for that pattern then .{5} means 5 of any character. 

(20[12345].{4}|R0000.{5})

 Hopefully this will help with other patterns you want to match with. I would check out Interactive Lessons - Alteryx Community Regex the community has some great videos on how to use Regex.

[JT0168]

would like to seek your help on this......as this was not captured in the previous workflow subject those with comma.. thank you!

This is the excel file that I have (please see below)

SKU	Ref Number 1	Ref Number 2
5317	20,420,270,002,041,500	20,420,270,002,041,500
2073	20,420,270,002,041,500	20,496,060,002,051,600
7264	20,420,270,002,041,500	20,471,610,002,048,700
9238	20,420,270,002,041,500	20,447,220,002,043,900
893	20,420,270,002,041,500	20,575,240,002,062,500
907	20,420,270,002,041,500	20,618,820,002,064,600
2025	20,420,270,002,041,500	2040097
30856	20,420,270,002,041,500	20,458,330,002,046,800
81284	20,420,270,002,041,500	20,531,130,002,055,200
6840	20,420,270,002,041,500	20,447,340,002,044,700

and this should be the output....

Ref 1 becomes ref 1A and 1B, I need those 7 digit that starts with 201 up to 210

Ref 2 becomes ref 2A and 2B, I need those 7 digit that starts with 201 up to 210

SKU	Ref Number 1_A	Ref Number 1_B	Ref Number 2	Ref Number 2_B
5317	2042027	2041500	2042027	2041500
2073	2042027	2041500	2049606	2051600
7264	2042027	2041500	2047161	2048700
9238	2042027	2041500	2044722	2043900
893	2042027	2041500	2057524	2062500
907	2042027	2041500	2061882	2064600
2025	2042027	2041500	2040097
30856	2042027	2041500	2045833	2046800
81284	2042027	2041500	2053113	2055200
6840	2042027	2041500	2044734	2044700

[IraWatt]

Hey @JT0168,

Very similar to the pervious solution you can do it like this:

This solution uses the transpose and cross tab tools which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how they work: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117#done

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com