Search for, then extract specific text from a string and separate into columns.

[jayhouse]

Hello, I am trying to figure out a method to extract upto 10 numbers from within a varied string length and print them out to the proceeding columns.
There will always be atleast one "Fail" in the line and i need the policy id Preceding. Then search for the next fail and print it out in the next column until there are no more.

The Policy number length can range between 6 and 11 characters, but will always be between quotations.

Here is an example of what i am working with.

---

[TABLE="class: grid, width: 1000, align: left"]
<tbody>[TR]
[TD]Input[/TD]
[TD]Fail 1[/TD]
[TD]Fail 2[/TD]
[TD]Fail 3[/TD]
[TD]Fail 4[/TD]
[TD]Fail 5[/TD]
[TD]Fail 6[/TD]
[TD]Fail 7[/TD]
[TD]Fail 8[/TD]
[TD]Fail 9[/TD]
[TD]Fail 10[/TD]
[/TR]
[TR]
[TD][{"PolicyId":"31.13.1","Result":"Skip"}],[{"PolicyId":"21.2.1","Result":"Fail","Notes":"blah blah blah blah.
","AdditionalItems":[]}][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][{"PolicyId":"31.32.11","Result":"Fail","Notes":"blah blah blah blah blah blah blah.
","AdditionalItems":[]}],[{"PolicyId":"6000.12.13","Result":"Fail","Notes":"blah blah blah blah.
","AdditionalItems":[]}][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

Give this a try, the formula in cell B2 should be copied across and down as needed:

Excel Workbook
	A	B	C
1	Input	Fail 1	Fail 2
2	[{"PolicyId":"31.13.1","Result":"Skip"}],[{"PolicyId":"21.2.1","Result":"Fail","Notes":"blah blah blah blah.","AdditionalItems":[]}]	21.2.1	*
3	[{"PolicyId":"31.32.11","Result":"Fail","Notes":"blah blah blah blah blah blah blah. ","AdditionalItems":[]}],[{"PolicyId":"6000.12.13","Result":"Fail","Notes":"blah blah blah blah.","AdditionalItems	31.32.11	6000.12.13
Sheet

 

[jayhouse]

How the.. Yeah this works!
I am a little confused how its working, but i guess i will need to watch the formula work and study up! Thank you for the hel

 

[István Hirsch]

You are welcome, thanks for the feedback.