Excel: Extract a 5 Digit Number From Inconsistent Text Strings


I know how to extract a number from a text string using: LOOKUP(99^99,--("0"&MID(J1, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, J1&"0123456789")),ROW($1:$10000)))) But in my current data set I have thousands of inconsistent text strings some of which contain other numbers that I don't care about - with the above formula it just grabs the first number it finds left to right. The number I need to grab is always a 5 digit number. Is there any formula I can use that would only grab out a 5 digit number ignoring all other numbers? Here's an example text string: "9/14/2010.NTL.TeleBroc.55129T_V1_N" where 55129 is the desired extract value but like I said the other text strings don't necessarily follow this example's format/delimiters. Thanks! Jeff


This question generated 18 answers. To proceed to the answers, click here.

This thread is current as of September 07, 2011.


For more resources for Microsoft Excel