Please help on Alteryx - how to extract part of the string with given conditions
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can use the Regex Tool set to Parse and use the expression,
(20[1-5].{4})
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
