Pull specific string of data from variable cell value

Hello!

I have a need for a formula based data extraction/verification method. I receive a list of values, which is not always in the correct format.

I need to extract a 7 digit string which is alpha for the first three characters, and numeric for the last four. For example, AAA0001 to ZZZ9999

The problem is, sometimes the number in the cell is surrounded by other characters of varying length, both letters and numbers (there will only be one occurrence of the number/format I need to extract), it could be the first 7 digits of a string, the last 7, somewhere in the middle. Sometimes it is separated by a character I can delimit by such as a "-", but when this is the case the number I need is not always in the same delimited location and ends up in in different columns on half of the rows.

Short version is I need a formula that will find a string of three letters followed by numbers and return that value, regardless of it's position within a cell.

Thanks in advance!

Thank you for the quick reply, Unfortunately I cannot unload any files from the machine I am using at the moment, however you can use the following comma separated text to easily build a sample sheet

Please Login or Register  to view this content.

you will see in row 4 the number is already what I need, which can happen. also row 6 contains no matching strings, and B6 is left blank as a result

Thanks!

AlKey,

Thank you for this and it does appear to be working exactly as needed, however I cannot use any VBA for this particular projec.I think this could be done formulaically... If there is no possible way to achieve this with formulas I will make myself a buffer sheet with your example and do some manual copy/paste work

Thanks again!