Solved: Difference between UDF & sub routine

[Digita]

Hi everyone,

Just a curious question. I have a UDF to extract numbers (digits 0-9) from a string.

Function XtractNumbers(ByVal Str As String) As Long
  Dim i As Long, k As Variant, j As Variant
  For i = 0 To Len(Str)
    k = Mid(Str, i + 1, 1)
    If Not k >= 0 Or k <= 9 Then
      j = j + k
    End If
  Next
  XtractNumbers = j
End Function

The above UDF returns #Value! when the cell contains "asdfds 17/8 15/05/2007"

I tried another tactic by writing a sub to do the same thing.

Sub test()
Dim i As Integer, k As Variant, j As Variant
mystring = "asdfds 17/8 15/05/2007"
  For i = 0 To Len(mystring)
    k = Mid(mystring, i + 1, 1)
    If Not k >= 0 Or k <= 9 Then
      j = j + k
    End If
  Next
  MsgBox j
End Sub

The sub provides the correct result "17815052007" in the msgbox. Why does the UDF not work?

Thanks & regards


KP

[Charlize]

[VBA]Function XtractNumbers(ByVal Str As String)
Dim i As Long, k As Variant, j As Variant
For i = 0 To Len(Str)
k = Mid(Str, i + 1, 1)
If Not k >= 0 Or k <= 9 Then
j = j + k
End If
Next
XtractNumbers = Val(j)
End Function[/VBA]

[Bob Phillips]

The problem is that Mid returns a string, and even though you use + this is not addition but concatenation of a string. You then try and drop it into a lomg, voila, #VALUE

An alternative to Charlize's solution

[vba]

Function XtractNumbers(ByVal Str As String) As Double
Dim i As Long, k As Variant, j As Double, l As Long
For i = Len(Str) To 1 Step -1
k = Mid(Str, i, 1)
If Not k >= 0 Or k <= 9 Then
j = j + k * 10 ^ l
l = l + 1
End If
Next
XtractNumbers = j
End Function
[/vba]

[anandbohra]

XLD & Charlize

what might be reason for this output (based on your codes)
the value i had given is this in cell A1
anand1deepaki2165a4sdf59as7dfas4d5f4a6sdf46sd4f3asdf1asdf7as6d54f6asd4f3a2s 3

=xtractnumbers(A1)
i got this output

121654597454646000000000000

[rory]

You only get 15 digits of precision in Excel. If you need more than that you will need to use a string and parse and work with it (if possible) accordingly.
Regards,
Rory

[Norie]

The problem is caused by an overflow error.

Either Charlize's or xld's suggestions should solve it.

[Digita]

Thanks Charlize & XLD. You're awesome.

Have a great day.

KP