How can I extract "logical_test" from an if statement in excel?

Question

I am compiling an Excel spreadsheet for calculations, and I need to show the formulas to go with the solutions, for the most part, quite simply, but when I come to the “if” formula in the excel cell, I don’t want to show value_if_true and value_if_false ... Just the value logical_test.

Example:

Formula: =if(and(5<=A1, A1<=10),"Pass", "Fail");

The result will be: "and(5<=A1, A1<=10)"

I need to be able to work with complex logic tests that may include nested if statements, so just comma-division will not work reliably. Similarly, the value_if_true and value_if_false statements may also contain if statements.

Any ideas?

Answer 1

, , - ( IF():

Function extrIf(ByVal ifstatement As Range) As String

Dim S$, sRev$, x%, k
S = Replace(Replace(ifstatement.Formula, "IF(", "\"), "),", ")|")

sRev = StrReverse(S)

If InStr(1, sRev, "|") > InStr(1, sRev, "\") Or InStr(1, sRev, "|") = 0 Then
    x = InStr(1, StrReverse(Left(sRev, InStr(1, sRev, "\"))), ",") - 1
    S = Mid(S, 1, Len(S) - InStr(1, sRev, "\") + x) & "|"
End If

sRev = ""

For Each k In Split(S, "|")
    If k <> "" Then
        If k Like "*\*" Then
            sRev = sRev & ", " & Mid(k, InStr(1, k, "\") + 1, 999)
        End If
    End If
Next

extrIf = Mid(sRev, 3, 999)

End Function

:

:

, , , .

Answer 2

If, ( ), , - = 0.

Function ExtractIfTest(Target As Range) As String
    Dim ch As String, s As String
    Dim openP  As Long
    Dim x As Long
    s = Target.formula

    For x = 5 To Len(s)
        ch = Mid(s, x, 1)
        If Mid(s, x, 1) = "(" Then
            openP = openP + 1
        ElseIf Mid(s, x, 1) = ")" Then
            openP = openP - 1
        ElseIf Mid(s, x, 1) = "," And openP = 0 Then
            ExtractIfTest = Mid(s, 5, x - 12)
        End If
    Next
End Function

, A1,B1. , (A1,B1)

Answer 3

UDF, . , , .

Function ExtractFormulaParameter(Target As Range, Optional Position As Long = 1) As Variant
    Dim inString As Boolean
    Dim formula As String
    Dim st As Long, sp As Long, i As Long, c As String
    Dim parenthesis As Long, comma As Long
    formula = Target.formula
    st = 0: sp = 0
    If Position <= 0 Then ExtractFormulaParameter = CVErr(xlErrValue): Exit Function

    For i = 1 To Len(formula)
        c = Mid$(formula, i, 1)
        If inString Then
            If c = """" Then
                inString = False
            End If
        Else
            Select Case c
            Case """"
                inString = True
            Case "("
                parenthesis = parenthesis + 1
                If parenthesis = 1 And Position = 1 Then
                    st = i + 1
                End If
            Case ")"
                parenthesis = parenthesis - 1
                If parenthesis = 0 And sp = 0 Then sp = i: Exit For
            Case ","
                If parenthesis = 1 Then
                    comma = comma + 1
                    If Position = 1 And comma = 1 Then sp = i: Exit For
                    If Position > 1 And comma = Position - 1 Then st = i + 1
                    If Position > 1 And comma = Position Then sp = i: Exit For
                End If
            Case Else
            End Select
        End If
    Next i

    If st = 0 Or sp = 0 Then
        ExtractFormulaParameter = CVErr(xlErrNA)
    Else
        ExtractFormulaParameter = Mid$(formula, st, sp - st)
    End If
End Function

By default, it returns the first parameter, but you can also return the second or third, and it should work with any formula.

Answer 4

Thanks for answers. I thought about it more and ended up coming up with a similar solution for the ones above - manipulating the string to extract the text where we expect to find a logical test.

It works well, and I'm sure I can use it to extract additional logic tests from substrings.