I am trying to extract the first letter.

Options

Hi Community,

I need to extract the first letter from other cell. Please check the formula and help me.

Please help me to get the correct values, If 'e' represents in the duration column then it should be '7' else '5'

Thanks In Advance!

Cheers!!

Tags:

Best Answers

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/03/23
    Options

    @Shaik. Baru You're almost there. The CONTAINS function looks anywhere in the cell for the value you're searching for. So it's not quite right for this formula. What you're looking for is the LEFT function, which returns the leftmost characters of the text value in the cell you're referencing. The syntax is:

    =LEFT(text value, number of characters to collect)

    So for your formula, you would use:

    =IF(LEFT(Duration@row, 1) = "e", "5", "7")

    • I am assuming you want your values of 5 and 7 to be considered as text since you have them wrapped in quotes in your formula. If that's not the case, and you want to be able to use these for calculations, remove the quotes: =IF(LEFT(Duration@row, 1) = "e", 5, 7)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Shaik. Baru
    Shaik. Baru ✭✭✭
    Options

    @Jeff Reisman

    Thanks for your comment. i would like to appreciate your help on this. But unfortunately this is not working 100%. I would like to see, If any cell data starts with "e"(elapsed) then it should reflect 7, and cell data without "e" should reflect 5.


    Thanks again!!

    Shaik

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/03/23 Answer ✓
    Options

    Sorry, I was going off the formula in your screenshot instead of your description of your logic.

    When using IF, the syntax is =IF(logical expression, value if true, value if false)

    Just switch the places of the 5 and 7 so that 7 is the value if true and 5 is the value if false:

    =IF(LEFT(Duration@row, 1) = "e", 7, 5)

    (The links to the help pages for Smartsheet Functions and Formula Error Messages are in my signature below. I find it very handy to have these bookmarked.)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Shaik. Baru
    Shaik. Baru ✭✭✭
    Answer ✓
    Options

    That helps,

    Thanks Jeff!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!