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

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.

Find more posts tagged with

Accepted answers

BullandKhmer

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

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.