Count special characters in a cell

I'm wondering if it's possible to count special characters in a cell, created from a cell formula

Count String Occurrence in a Cell — Smartsheet Community

What I'm currently doing is adding a period to a JOIN(COLLECT function which I am counting the periods. See below:

=JOIN(COLLECT(Post$1:Post$300, [Position Status]$1:[Position Status]$300, "Green", 
[Name Selection]$1:[Name Selection]$300, ""), "." + CHAR(10))

Where this is the formula counting the periods:

=((LEN([SITE / LOCATION]344) - LEN(SUBSTITUTE([SITE / LOCATION]344, ".", ""))) / LEN(".")) + 1

However, what I'd like to do is count the CHAR(10). I don't think it's possible, but it's worth asking the community. Let me know!

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey @SteyJ

You can count CHAR(10). I tested this formula

=LEN(JOIN(COLLECT(Post:Post, [Position Status]:[Position Status], "Green", [Name Selection]:[Name Selection], ""), "." + CHAR(10))) - LEN(SUBSTITUTE(JOIN(COLLECT(Post:Post, [Position Status]:[Position Status], "Green", [Name Selection]:[Name Selection], ""), "." + CHAR(10)), CHAR(10), ""))

Will this work for you?

Kelly

All comments

Kelly Moore

Hey @SteyJ

You can count CHAR(10). I tested this formula

Will this work for you?

Kelly

SteyJ

https://community.smartsheet.com/discussion/comment/412125#Comment_412125

@Kelly Moore Amazing solution! wow!! Thank you very much

I modified it slightly to suite my needs:

=LEN(JOIN(COLLECT(Post:Post, [Position Status]:[Position Status], "Green", 
[Name Selection]:[Name Selection], ""), CHAR(10))) - 
LEN(SUBSTITUTE(JOIN(COLLECT(Post:Post, [Position Status]:[Position Status], "Green", 
[Name Selection]:[Name Selection], ""), CHAR(10)), CHAR(10), "")) + 1

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.