Count multiple strings from one cell (not a dropdown list column)

Lisa Matthews

Hello,

Please help with a formula to count strings in a cell. I receive an Excel export file which contains a string of values in a cell. This is my objective (and a few notes):

1) Subitems Column. Every single value is unique and there are a few thousand.

a) Count total number of values in the cell (almost like a "word" count?). Do not need to single out unique values.

I tried to convert the cell into a dropdown list to see if it would separate the values and then create a simple count formula but that didn't work. The result is each entire cell string is created as a "value" in the dropdown list.

Thank you for your help. This community is full of super smart, kind and helpful people!

--Lisa M.

BullandKhmer

Actually to avoid issues with blank rows you might want to go with

=IFERROR((LEN(subitems@row) + 2) / 12,0)

BullandKhmer

So it looks like every unique number is 10 characters + commas and spaces right?

Something like the below should get the job done:

=(LEN(subitems@row) + 2) / 12

BullandKhmer

Actually to avoid issues with blank rows you might want to go with

=IFERROR((LEN(subitems@row) + 2) / 12,0)

Lisa Matthews

Hello @BullandKhmer ,

This formula worked perfectly for me! Thank you so much!

--Lisa