Count special characters in a cell

SteyJ
SteyJ ✭✭✭✭✭✭
edited 01/05/24 in Formulas and Functions

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!

Sincerely,

Jacob Stey

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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

    · Share on FacebookShare on Twitter

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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

    · Share on FacebookShare on Twitter
  • SteyJ
    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
    Sincerely,

    Jacob Stey

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!