Excel VBA: How to get UDF to return 0 when conditions aren't met

user_12160576 0
Viewed 74

I've been looking all over the place to find a simple UDF that I can use to extract only the number from any location in a cell, even if it contains a decimal. The good news is, I'm not asking anybody to write one for me because I've found two that are 99% of the way there.

The first function, GetNumeric, will ignore the decimal when it returns the value. As in, it will return "775" if the referenced cell contains "7.75 days" However, if the referenced cell does not have any numbers, such as "dogs", it will return "0"

The second function, GetNum, solves the decimal issue, and returns "7.75" but now if the referenced cell does not have any numbers it will return an error.

Function GetNumeric(CellRef As String)
    Dim StringLength As Integer
    StringLength = Len(CellRef)
    For i = 1 To StringLength
        If IsNumeric(Mid(CellRef, i, 1)) Then result = result & Mid(CellRef, i, 1)
    Next i
    GetNumeric = result
End Function

Function GetNum(ByVal InString) As String
    Dim x As Integer
    For x = 1 To Len(InString)
        If Mid(InString, x, 1) Like "[!0-9.() ]" Then Mid(InString, x, 1) = Chr$(1)
    Next
    GetNum = Trim(Replace(InString, Chr$(1), ""))
End Function

I figured I could just put the "Like" portion in the first function, but then it always returns 0. Why is this?

user-defined-functions vba excel
1 Answers

The second function will not return a value if there is no number in the cell. It will return an empty cell, but I would like it to return a 0, like the first function does.

Is this what you are trying?

This checks in the end if the returned value is not empty. And if it is empty then pass 0 to it.

Function GetNum(ByVal InString) As String
    Dim x As Integer
    Dim Ret As String
    
    For x = 1 To Len(InString)
        If Mid(InString, x, 1) Like "[!0-9.() ]" Then Mid(InString, x, 1) = Chr$(1)
    Next
    Ret = Trim(Replace(InString, Chr$(1), ""))
    
    If Len(Trim(Ret)) = 0 Then GetNum = 0 Else GetNum = Ret
End Function
user_11405790
Related