Partial text search

Jessica Howell

Hi All!

I'm looking for a formula that does a partial text search, counting shift codes containing "AS." The column will appear as below, but I want the formula to only consider the AS.

It will need to be added to the formula below,

=COUNTIFS({USER ID}, [Team/ Colleague ID's]@row, {Period2}, [Period ]@row, {year2}, Year@row, {Assem%}, >=0.95, {STD}, >=0.84)

Is this possible? Any suggestions?

Paul Newcome

You would use something along the lines of:

=COUNTIFS({USER ID}, [Team/ Colleague ID's]@row, {Period2}, [Period ]@row, {year2}, Year@row, {Assem%}, >=0.95, {STD}, {Shift Code}, LEFT(@cell, 2) = "AS")

Paul Newcome

You would use something along the lines of:

=COUNTIFS({USER ID}, [Team/ Colleague ID's]@row, {Period2}, [Period ]@row, {year2}, Year@row, {Assem%}, >=0.95, {STD}, {Shift Code}, LEFT(@cell, 2) = "AS")

Andrée Starå

Hi @Jessica Howell

I hope you're well and safe!

Will the AS part always be the first two characters?

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jessica Howell

@Paul Newcome thank you! i will give that a try

@Andrée Starå Hi Andree!

Yes, AS will always be included. In other columns, it will be other variations of letters

Paul Newcome

Happy to help. 👍️

Jessica Howell

Morning @Paul Newcome I tried what you suggested, it came up as #unparseable

=COUNTIFS({USER ID}, [Team/ Colleague ID's]@row, {Period2}, [Period ]@row, {year2}, Year@row, {Assem%}, >=0.95, {STD}, >=0.84, {shift code},LEFT(@CELL,2) ="AS")

Paul Newcome

"@cell" should be all lower case.

Jessica Howell

@Paul Newcome such a simple mistake... it appears to be working now :) Thanks so much!!

Paul Newcome

Happy to help. 👍️