I am using the following code to extract a 5 digit number from a
string. It does not work. If there is a longer than 5-dgit number in
front of the 5-digit number it gives the first 5 digits of this longer
number (while it only should give the 5-digit number, not a part of a
longer number if the text string contains it).

Text sting Result
365485 12345 36548 Wrong, this one should
give 12345

++++++++++++++++++++++++++++++++++++++++++++
Function Extract(S As String) As String
Sub test()
Dim bArr() As Byte

Dim vIn

vIn = Application.InputBox("Enter 10 digit number")
bArr = StrConv(vIn, vbFromUnicode)
For i = 0 To UBound(bArr)
Select Case bArr(i)
Case 48 To 57
Case Else
bArr(i) = 32
End Select
Next

vIn = StrConv(bArr, vbUnicode)

' Replace n/a in xl 97, use Application.Substitute
vIn = Replace(vIn, " ", "")

MsgBox vIn & vbCr & _
IIf(Len(vIn) = 10, "OK", "Bad input person")

End Sub

End Sub

End Function

+++++++++++++++++++++++++++++++++++++++++++++++++
Text sting Result
36548 dfg hdh 515748 36548
fgj 26547 152475 12-11-2005 26547
12345 12345
dfgdg 21212 .21 dfgdg . - dfgdfg 21212
blablabla
365485 12345 36548 Wrong, this one should
give 12345
12254 12254
1,2547 blabla -12457 12457
1.2547 blabla 12


I'm out of ideas. Does someone know a solution?

Thanks in advance !

Chris