Trying to do logic if first character is a number, IF(ISNUMBER(LEFT... seems to always result in FAL

mynameisc
mynameisc
edited 12/09/19 in Formulas and Functions

I want to do some logic based on whether an entry starts with a number.  I assumed `=IF(ISNUMBER(LEFT([Primary Column]1)), "Number", "No Number")` would have worked, but it seems to always return FALSE.  What am I missing?

(Also, does this forum not support tables or code formatting?)

| Primary Column               | Formula                                                      | Result    |

|------------------------------|--------------------------------------------------------------|-----------|

| 555 I start with a number    | IF(ISNUMBER(LEFT([Primary Column]1)), "Number", "No Number") | No Number |

| I do not start with a number | IF(ISNUMBER(LEFT([Primary Column]1)), "Number", "No Number") | No Number |

|                              |                                                              |           |

|                              |                                                              |           |

Comments

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi,

    If you have a cell with both text and numbers in your primary column, it will be read as one long text string. In order to find if it starts with a number, you may need to set up a helper column to pull the first character from your Primary column and return it specifically as a Value (this Value formula will only work if it's a number, otherwise you will get an error message).

    Then in your current formula column, you could adjust your formula to reference this second, helper column to check to see if it's a number or not, adding an IFERROR formula in front to say that it's not a number and there's an error, return "No Number". 

     

    First example Formula, looking for the first character in the primary column:

    =VALUE(LEFT([Primary Column]@row, 1))

     

    Second example Formula, looking at the secondary helper column:

    =IFERROR(IF(ISNUMBER([Helper Column]@row), "Number"), "No Number")

     

     

    (Also! As a side-note, you may have noticed the banner stating that we will be making some updates to the Community forum in the coming month. One of those updates will enable you to write posts using Code format! Stay tuned.)

    Screen Shot 2019-11-20 at 11.45.07 AM.png

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!