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

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

LEllis

https://community.smartsheet.com/discussion/comment/360314#Comment_360314

Wow, this is amazingly simple and appears to do what I need. You have no idea how complicated I was making this! Thank you so much.

Julie Fortney

@LEllis You're welcome! I know just what you mean, but I've found that all that trial and error usually helps me learn something that comes in handy later on.

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

Nat O

Thanks @Julie Fortney This just saved me a bunch of headache searching and figuring out too!!