extracting numeric string?

[Mike Romanovich]

I have tables of product names such as :

poly flex 99103M
multi bond 74210P industrial
Xylophene tetra 12345X

I need to extract only the 5 digit numeric string from each name and
place it in an adjoining column. the name of the Items is variable.
There must be a simple VBA solution to this, but it is beyond my
meager abilities. Any suggestions would be appreciated.

[Ajay Askoolum]

Try:

Function GetNumber(ByVal Content As String) As Long
Dim i As Integer, s As String
For i = 1 To Len(Content)
If 0 <> IsNumeric(Mid(Content, i, 1)) Then s = s &
Mid(Content, i, 1)
Next i
If IsNumeric(s) Then GetNumber = Val(s)
End Function

NOTE: Assumes embedded number is integer; if there is no
number 0 is returned.

CODE should go into a module, you'd use it as. e.g.
=GetNumber(A1)

>.
>

[Mike Romanovich]

I've found occurences of non-numeric characters in the strings I need.
Is it possible to modify that function so that I could search for the
first numeric character and then add the following 4 characters
whether text or numeric?

[Myrna Larson]

Function GetNumber(ByVal Content As String) As String

Dim i As Integer, s As String
For i = 1 To Len(Content)

If IsNumeric(Mid(Content, i, 1)) Then
GetNumber = Mid$(Content, i, 5)
Exit Function
End If
Next i
End Function

Or, if the "words" are always separated by spaces and you are using XL2000 or Xl2002, and what
you actually want is the first "word" that begins with a digit, regardless of its length,

Function GetNumber(ByVal Content As String) As String
Dim Words() As String
Words() = Split(Content, " ")
For i = 0 To UBound(Words())
If IsNumeric(Left$(Words(i), 1)) Then
GetNumber = Words(i)
Exit Function
End If
Next i
End Function