Hi Director,

Try:

'=============
Public Function Last5Digits(sStr) As Variant
Dim i As Long
Dim j As Long
Dim sStr2 As String
Dim sOut As String

For i = Len(sStr) To 1 Step -1
sStr2 = Mid(sStr, i, 1)
If IsNumeric(sStr2) _
And Not sStr2 = vbNullString Then
sOut = sStr2 & sOut
j = j + 1
If j = 5 Then Exit For
End If
Next i

If sOut < vbNullString Then
Last5Digits = CLng(sOut)
Else
Last5Digits = CVErr(xlErrNA)
End If
End Function
'<<=============



---
Regards,
Norman


wrote in message
oups.com...
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