Excel Pattern Matching

[MartinAustin]

Hello everyone --

I have a deadline on monday for a project that seems as though regular expressions would fit the bill (though I am willing to try anything at this point).

The issue is cleaning a certain column of data of erroneous characters. The column represents an inbound shipments PO number, and can only be comprised of 7 numerical characters in a row. Here are some examples of what we get in this field:

7777-1234567-99
77,1234567-00

Etc, etc. The 1234567 is the valid PO number, and all I want to do is find the first occurence of 7 numerical characters in a row, store it in a variable, erase the current cell value, and place the 7 digit PO number back into the same field.

Ones that do not match should be copied into another sheet in Excel (which I can do no problem), but I'm having a hell of a time figuring this one out.

Can someone out there get me started? Thanks, and God Bless!

[edit: updated title since it doesn't have to be a regular expression solution, as long as it works! )]

 

[MartinAustin]

Actually, I have one follow up question. What I'd like to do is move the clean POs to another sheet, and the dirty POs to their own sheet as well. I can do this, though it is quite a roundabout and ungainly method.

Is there an easy method to do this?

Thanks!