Count the number of times a text value appears in a single cell (or alternatively, split the cell)

LEllis

Hi Smartsheets community,

I have a column of data with cells that contain text values that I need to count. For example, the cell in one of these columns may contain something like:

"ABC123 - TEXT TEXT TEXT

ABC008 - TEXT TEXT TEXT"

The two lines of "ABC" text are separated by a carriage return.

I'm trying to count how many times each "ABC000" value occurs in my spreadsheet. So I need to know how many times we find ABC001, ABC002, and so on. Sometimes, there is only one "ABC" value in a cell, sometimes there are two or more.

Any suggestions on how to do this? Smartsheets does not seem to have a SPLIT function, where I could split out the values via the carriage return and then attempt to count them that way.

Thanks in advance.

Julie Fortney

Hi @LEllis,

A good way to do this would be to create a sheet with each ABC value in a column, then add a column with a cross-sheet formula to count the number of times each value appears on your original sheet.

Here's my pretend original sheet:

And here is the new sheet to count the ABCs:

The count column formula is:

=COUNTIFS({Data column on original sheet}, CONTAINS([Primary Column]@row, @cell))

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt