UDF Function not working

[omairhe]

Hey everyone,

I have a UDF that would collect a set of 9 numeric digits.

For instance:
A1 = Hello123456789?/ok
B1 = GetNumber(A1)

UDF result:
B1 = 123456789

Function GetNumber(ByVal S As String) As Variant
Dim X As Long, Num As Variant
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  For Each Num In Split(Application.Trim(S))
    If Len(Num) > 8 Then
      If GetNumber > 0 Then
        GetNumber = CVErr(xlErrValue) 
        Exit Function
      Else
        GetNumber = CLng(Num)
      End If
    End If
  Next

End Function

But now I am trying to retrieve bigger values like
A1 = 23ALPHA100050822112345678930047839750ZEBRA36
B1 = GetNumber(A1)

UDF result:
B1 = #VALUE !

Required result:
B1 = 100050822112345678930047839750

Will appreciate any help. Thank you.

<tbody>
</tbody>

 

[mse330]

Try to change the below line of your code

'From this
GetNumber = CLng(Num)

'To This
 GetNumber = CStr(Num)

 

[Rick Rothstein]

Here is a UDF (user defined function) that will return the largest number in the string passed into it...

[table="width: 500"]
[tr]
	[td]Function LargestNumber(ByVal S As String) As String
  Dim X As Long, Nums() As String
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  Nums = Split(Application.Trim(S))
  For X = 0 To UBound(Nums)
    If Len(Nums(X)) > Len(LargestNumber) Then LargestNumber = Nums(X)
  Next
End Function[/td]
[/tr]
[/table]

 

[omairhe]

Thank you for your suggestions, appreciate it.