Parse data into multiple rows based on partial field name

[MarkD]

I'm using the download tool to access an API, then the JSON Parse tool, and then this combination of tools to improve it , but still need to get further with it, and not quite sure how to best approach it, here is what I'm getting in my output: 

FirstName	LastName	projects_0_id	projects_0_name	projects_0_total	projects_1_id	projects_1_name	projects_1_total	projects_2_id	projects_2_name
John	Smith	1	Widgets	189.4	2	Gadgets	248.9	3	Stones

and so on, for hundreds of projects, instead what I would want:

FirstName	LastName	Project_ID	Project_Name	Project_Total
John	Smith	1	Widgets	189.4
John	Smith	2	Gadgets	248.9
John	Smith	3	Stones	18.2

Any help on how to accomplish this would be greatly appreciated! Thanks in advance!

[Assaf_m · Accepted solution]

Check the attached solution.

let me know if works for you

[MarkD]

Thanks! Had to make some small tweaks since this didn't work with project numbers longer than 1 character (i.e. failed at project 10+), but got it working! Regex was the best solution as taking 2 characters created other challenges with some fields with the underscore and some without.

Thanks again!