title: Excel 2007 find the largest number in text string
body: <p>I have Excel 2007. I am trying to find the largest number in a cell that contains something like the following:</p>
  
  <p>[[  E:\DATA\SQL\SY0\  , 19198 ],[ E:\  , 18872 ],[ E:\DATA\SQL\ST0\  , 26211 ],[ E:\DATA\SQL\ST1\  , 26211 ],[ E:\DATA\SQL\SD0\  , 9861 ],[  E:\DATA\SQL\SD1\  , 11220 ],[ E:\DATA\SQL\SL0\  , 3377 ],[  E:\DATA\SQL\SL1\  , 1707 ],[ E:\DATA\SQL_Support\SS0\ , 14375 ],[  E:\DATA\SQL_Support\SS1\  , 30711 ]]</p>
  
  <p>I am not a coder but I can get by with some basic instructions.  If there is a formula that can do this, great! If the best way to do this is some sort of backend code, just let me know.  Thank you for your time.</p>
  
  <p>I do have the following formula that almost gets me there:</p>
  
  <pre><code>=SUMPRODUCT(MID(0&amp;A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&amp;LEN($A$2))),1))*ROW(INDIRECT("1:"&amp;LEN($A$2))),0),ROW(INDIRECT("1:"&amp;LEN($A$2))))+1,1)*10^ROW(INDIRECT("1:"&amp;LEN($A$2)))/10)
  </code></pre>
  
  <p>With a cell that contains a string like above, it will work. However, with a string that contains something like:</p>
  
  <p>[[  E:\DATA\SQL\SY0\  , 19198.934678 ],[ E:\  , 18872.2567 ]]</p>
  
  <p>I would end up with the value of 19198934678 as the largest value.</p>