search

joshbtn / excelFormulaUtilitiesJS

Based on ewbi's go calc excel formula parser http://ewbi.blogs.com/develops/popular/excelformulaparsing.html. This library contains methods to beautify an excel formula. This library also contains functions to convert an excel formula to JavaScript (ECMAScript) or C# and python.
http://excelformulabeautifier.com/
Other
394 stars 101 forks source link

Troublesome formula #66

Open glawrence opened 6 years ago

glawrence commented 6 years ago

I put in the following formula:

=IF(IF(ISERROR(SEARCH("bh", A6)), -1, SEARCH("bh", A6)) >=0, "Birmingham", IF(IF(ISERROR(SEARCH("sh", A6)), -1, SEARCH("sh", A6)) >=0, "Sheffield", IF(IF(ISERROR(SEARCH("lon", A6)), -1, SEARCH("lon", A6)) >=0, "London", "Unknown")))

However the -1 values do not come beautify very well and seems to cause a problem

I know we could debate formatting for ever but with the above I think the following looks better than the actual output:


=IF(
    IF(
        ISERROR(
            SEARCH("bh", A6)
        ),
        -1,
        SEARCH("bh", A6)
    ) >=0,
    "Birmingham",
    IF(
        IF(
            ISERROR(
                SEARCH("sh", A6)
            ),
            -1,
            SEARCH("sh", A6)
        ) >=0,
        "Sheffield",
        IF(
            IF(
                ISERROR(
                    SEARCH("lon", A6)
                ),
                -1,
                SEARCH("lon", A6)
            ) >=0,
            "London",
            "Unknown"
        )
    )
)
joshbtn commented 6 years ago

I see what you are saying, the -1 doesn't align consistently. I've flagged this as a feature and I'll dig in when I get though some of the other bugs. Thanks for the feedback!

joshbtn commented 6 years ago

This is actually the same issue seen in issue #59