Need a solution for a Match Contains function

Philip Kolmar

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

Philip Kolmar

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

=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

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

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

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