Is there a way to create a formula that only uses part of the information in a cell?

I am trying to count the total number of times a name is used in a cell that is a free text field and could have multiple names. Is there a formula that will allow me to do that? I have tried tried CONTAINS and OR functions but neither lets me pull specific values. Below is an example of a formula that works, but the work put into it was time consuming.

In the example, I am trying to get the count for each time Prisma is used:

=COUNTIF([Employer Not Listed]:[Employer Not Listed], OR(@cell = "Prisma Health", @cell = "Prisma health, Pruitt Health, MUSC", @cell = "Prisma Health, Spartanburg Regional", @cell = "Prisma Children's Hospital", @cell = "Prisma", @cell = "Bon Secours or Prisma Health", @cell = "Lexington Hospital, Prisma or MUSC", @cell = "Medical University of South Carolina, Roper Hospital, Prisma Health, Lexington Medical Center", @cell = "MUSC, Summerville Medical Center, Trident Medical Center, Prisma Health", @cell = "AnMed or Prism", @cell = "Prisma Tuomey or Carolina Pines", @cell = "Anmed, Prisma Health"))

I appreciate any help you can provide.

Thank you,



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!