Invalid Procedure Call Or Argument (Error 5)-CodePudding

I have an excel with this kind of data: 5.25 Kg. However, I only need work with the number part of the data. For that purpose, I've created a function to extract only the number which I am interested in. I've tried to use it in the next procedure but it produces the "invalid procedure call or argument (Error 5)" and I don't get what is the root of the problem. Any help? If you have another idea how to extract easily the number is also welcome :)

Sub ExtractNumbers()
Dim m As Long
For m = 2 To 38
Worksheets("Hoja2").Cells(m, "Q").value = (DeleteUnits(Worksheets("Hoja1").Cells(m, "L").value))
Next
End Sub

Private Function DeleteUnits(value As String)
DeleteUnits = Left(value, Len(value) - 3)
End Function

CodePudding user response：

Assuming there's always a space between the Number and the Units, you don't need vb. This excel formula will work fine:

 =VALUE(LEFT(A3,SEARCH(" ",A3)))

Obviously, the formula is extracting the value from cell A3.

If there may or may/not be a space, this vb will do the trick:

Public Function ExtractValue(FromCell As Range)
    
    Dim in1%

    For in1 = 1 To Len(FromCell)
        If Not Mid(FromCell, in1, 1) Like "[.0-9]" Then Exit For
    Next in1
    ExtractValue = Val(Left(FromCell, in1 - 1))

End Function

If there may/may not be leading spaces, then you need one more bit of logic:

Public Function ExtractValue(FromCell As Range)
    
    Dim in1%, vnVal

''' Belt and braces: Remove any spaces
    vnVal = Replace(FromCell, " ", "")
    
    For in1 = 1 To Len(vnVal)
        If Not Mid(vnVal, in1, 1) Like "[.0-9]" Then Exit For
    Next in1
    ExtractValue = Val(Left(vnVal, in1 - 1))

End Function