Need a solution for a Match Contains function

Options

Hello!

I have a row of months and I just need to return which position in the range of cells contains a month of the year since those cells also contain additional text.

Basically, if my consecutive cells are "January - In Progress", "February - Complete", "March - In Progress" I need a match that looks for which cell contains February and for it to return 2 since the second cell in the range contains February.

Thank you for any help you can provide!


Pip

Answers

  • Philip Kolmar
    Philip Kolmar ✭✭✭✭
    Options

    As a follow-up, the Month name is always the first word in the string. If it's easier to try to match against the first word in a string, could someone provide guidance there?


    Thank you!

    Pip

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    =substitute(index(collect({monthcol},{monthcol},left(@cell,3)="Feb"),1),"February - ","")


    Give that a try, replacing the {monthcol} reference with the column that has the values you are parsing.

  • Philip Kolmar
    Philip Kolmar ✭✭✭✭
    Options

    Thank you @L_123 for providing this! I'm actually not looking to return the rest of the string following "February - " I'm looking for it to find a cell in a range that contains "February" and return that cell position similar to how you'd do a match. In Excel, it would be =MATCH([*February*, {months}, 0) but the asterisks don't work in Smartsheet.

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    Oh, sorry, I misunderstood. I don't think this is possible with any normal application of smartsheet formulas without adding a helper column. I can think of a couple solutions where you join the line together then use text parsing to get a solution, but that seems like to much effort and to complicated a solution to avoid a single helper.

  • Philip Kolmar
    Philip Kolmar ✭✭✭✭
    Options

    @L_123 Thanks for your insight! I'll consider some solutions with helper columns.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!