That works amazingly well! (and I do have you credited and documented
in my code for all the help you have given me!) It did present an
offshoot puzzle though. The filenames with all the numbers are going
in column A. This code will pull out the product number in column B.
Column C will be the Product Description, and I've got a simple
vlookup formula for that. The vlookup formula looks up the Product
Number in column B and compares to a list on Sheet2. Sheet2 is simply
a list of all the product numbers and the corresponding product
descriptions.

So, it seems it would be a straight forward solution. But there's
something with the formatting of the Product Numbers after being
extracted via the code below that errors out (#NA) the vlookup
formula. I've tried copy/pasting the column as text, as general, as
number, and doing the same to the numbers on Sheet2, to no avail. If
I take the exact same number on Sheet2 and paste it over the number on
Sheet1, then the formula works. The Product Number doesn't APPEAR to
change at all. But it does. Any thoughts on that? If I can't get
the product description, it's not the end of the world - this code
below gets me to where the users most basically need to be. But it
would be helpful to be able to add the description.

Thanks again Clause!

Frank

On Tue, 21 Jul 2015 16:43:20 +0200, Claus Busch
wrote:

Hi Frank,

Am Tue, 21 Jul 2015 13:53:07 +0200 schrieb Claus Busch:

Sub Extract()

or try it this way:

Sub Extract()
Dim rngC As Range
Dim LRow As Long, i As Long
Dim varTmp As Variant, varSign As Variant
Dim myStr As String

LRow = Cells(Rows.Count, 1).End(xlUp).Row
'Here you can add other characters to be replaced
'But keep comma with space at position #0
varSign = Array(", ", ",", "-", " ")

For Each rngC In Range("A1:A" & LRow)
myStr = rngC
For i = LBound(varSign) To UBound(varSign)
myStr = Replace(myStr, varSign(i), "|")
Next

varTmp = Split(myStr, "|")

For i = LBound(varTmp) To UBound(varTmp)
If IsNumeric(Left(varTmp(i), Len(varTmp(i)) - 1)) _
And Len(varTmp(i)) 8 Then
rngC.Offset(, 1) = varTmp(i)
Exit For
End If
Next
Next
End Sub


Regards
Claus B.