Finding text in a string and evaluating a result

[roryok11]

I've got this long string in cell A1 of a worksheet in Excel 2007: <table style="width: 930px; height: 47px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="64"></colgroup><tbody><tr height="19"> <td class="xl63" style="height:14.4pt;width:48pt" height="19" width="64">{"1":{"7":"20"},"2":{"2":"6"},"3":{"4":"10"},"4":{"5":"15"},"5":{"1":"3"},"6":{"3":"9"},"7":{"6":"18"}}<wbr><wbr><wbr></td> </tr></tbody></table>and want to find text within individual pairs of curly braces.
Let's take the group {"5":"15"} as an example: I need to find the text {"5" and then hop to the right and return whatever text is between the following set of double quotes.
I think I can work with finding the first string using MID and FIND but cannot fathom out how to then jump to the adjacent pair of quotes to return what is within them.
Can anyone offer any suggestions please?

 

[Ron Coderre]

With your sample text in A1
and
D1: the text to find...e.g. {"5"

This regular formula returns the quoted single-character value that follows the the braced phrase that contains the D1 value

E1: =MID(A1,FIND(",",A1,FIND(D1,A1))+2,1)

In your example, that formula returns: 5

If D1 contains {"2", the formula returns: 3

Is that something you can work with?

 

[barry houdini]

I'm assuming you want to find 15. Given that that number can be any length try this formula

=MID(A1,FIND("{""5""",A1)+6,FIND("}",A1,FIND("{""5""",A1))-FIND("{""5""",A1)-7)

 

[Ron Coderre]

If Barry is correct (which he probably is)
and
there is a defined limit on the size of the number to find...like it could be anywhere between 0 and 99999

Then try this regular formula:

=LOOKUP(10^10,--LEFT(MID(A1,FIND(":",A1,FIND(D2,A1))+2,5),{1,2,3,4,5}))

Does that help?

 

[roryok11]

Thank you both so much for your help. Would never have figured out how to jump to the adjacent "pair" and extract the result.

I finally ended up with the following:
=MID($A$1,FIND(":{""5""",$A$1)+7,FIND("}",$A$1,FIND(":{""5""",$A$1))-FIND(":{""5""",$A$1)-8)

I had to make some slight changes to cater for that pesky initial { at the far left and therefore asked it to find :{ instead. Then incremented the +6 and -7 by one to cover the inserted colon in the formula.

Thanks to yourselves it now works as I wanted but didn't know the "how"