Hi Frank,

Am Tue, 21 Jul 2015 06:36:26 -0400 schrieb :

1234001000-234156-357248 Screw (loose)

This breaks down to:
Product ID-investigation#-inquiry# Component (issue)

So, I need to extract that first number. But, it's not just that
simple. Here are the few complications:

1) That larger number may be anywhere in the string.
2) That number may have a letter suffix (e.g., 1234001000E)
3) Most people use dashes, but some use commas or spaces

try:

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

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
If InStr(rngC, "-") Then
varTmp = Split(rngC, "-")
ElseIf InStr(rngC, ",") Then
varTmp = Split(rngC, ",")
ElseIf InStr(rngC, " ") Then
varTmp = Split(rngC, " ")
End If
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

If the macro does not work as expected please post more examples of
possible strings.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional