Count special characters in a cell

SteyJ

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!

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

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

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