RegEx - Split to Rows Second Instance of a Number

[Kristie_Pires]

Hello community! 

I need help using the RegEx tool to split to rows the second instance of a number. Whenever there is a second number on the Request Type field, that information should be moved to another row. I can do the additional clean up needed. 

For example, I have the first table and want what's on the second table.

ID	Request Type	Start Dt	Colleague
1	2 - Letter and 10 - Annual Review	6/6/2023	Ana
2	10- Annual Review	6/4/2023	Peter
3	3 - Waive Letter & 2 - Letter	6/3/2023	John

ID	Request Type	Start Dt	Colleague
1	2 - Letter	6/6/2023	Ana
1	10 - Annual Review	6/6/2023	Ana
2	10- Annual Review	6/4/2023	Peter
3	3 - Waive Letter	6/3/2023	John
3	2 - Letter	6/3/2023	John

[jdminton]

I would actually approach this with a different set of tools. There are some assumptions on my part, however.

1. The 10- Annual Review and the 10 - Annual Review (with space) was a typo and not from your actual data. This could be changed in the workflow to accommodate though.
2. The options are a defined list and not manually added by a user in free text format.
3. You can obtain the defined list.

By joining the Request Type options available to the data, you can use the formula tool to identify any of those options contained in the request data. You would then filter out the options that are not in the request and summarize.

Take a look and let me know if this works for you.

[acarter881 · Accepted solution]

Hello, @Kristie_Pires.

Please see the attached workflow for one method of doing this.

[Kristie_Pires]

Thank you both for helping me out! @acarter881 solution's works best for my case.

@jdminton the Request Type field is manually written by the users, so I have no control over how they spell certain words.  

Again, thank you!!! 

[Dina · Accepted solution]

Might have a simpler way, but this is my thought.