Hi. I've got a macro that imports the filenames of a users
investigation log into column A of a workbook and also hyperlinks
those files. Works well. I have a need to extract the product number
from each filename and put it in column B. Here is an example
filename:

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

The other numbers in the string are currently 6 digits, but at some
point it will go to 7 and maybe 8 (but not for a long time for 8).

I've found formulas and even VBA (and UDF) to extract a number, but
it's the variables that are complicating this for me. I'm thinking
maybe a a case structure or something along those lines, but how to
keep this flexible is eluding me. Any ideas? Thanks!

Frank

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

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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.

Hi Frank,

Am Tue, 21 Jul 2015 17:30:57 -0400 schrieb :

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.

check the Product Number in sheet2 for leading or trailing spaces and
delete them with TextToColumns = Fixed Width = Finish.
If that is not the solution please provide some examples.


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

That did the trick. All is well, and the thing works great! This was
a key piece to the puzzle. This project will now be almost
universally accepted by everyone because they can incorporate most of
their old methods with the new methods. Thanks so much!!

Frank

On Wed, 22 Jul 2015 07:39:11 +0200, Claus Busch
wrote:

Hi Frank,

Am Tue, 21 Jul 2015 17:30:57 -0400 schrieb :

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.

check the Product Number in sheet2 for leading or trailing spaces and
delete them with TextToColumns = Fixed Width = Finish.
If that is not the solution please provide some examples.


Regards
Claus B.