Finding a "." in a cell has me perplexed with an "!"


Okay, bad pun, sorry. It's been a rough search today.

I have an ID field for tracking Risks across projects. Each risk has a whole number: 1, 2, 15, 22, etc.

Each mitigation is a sub-number of the risk: 1.1, 1.1.1, 1.1.2, 15.1, 15.2, etc.

It isn't easy grouping them together, and auto-number to do this, so I wanted to have the Risks (all should be whole numbers, and not contain a ".") identified so that I could use that formula to automatically check a box, indicating all the Risks, and not the identified mitigation steps.

I have tried several variations, and none of them seem to allow me to check for a "." (period) in the field. I tried to back up to the simplest function, just to test for the "." first, and came up with:

=COUNTIF([rID]@row, CONTAINS(".", @cell))

The problem is that I get a count of zero no matter what is in the cell: <blank>, a whole number, or the desired results (the highlighted cells).

What is even more perplexing in my attempt at problem-solving, is that when there is more than one period, such as in 1.2.1 (which would indicate the first step in mitigation 2), it does find it:

Is there something that I am missing, or could do differently to get the result that I am looking for? Trying =IF(HAS([rID]@row, "."), "1", "0") produces worse results: 0 for all of them!

I didn't know there were so many was to try to solve the problem, almost as many as Excel in fact, but it's frustrating that none of them seem to be working.



  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Bill in Ohio

    The confusion is that ones with 1 period are looked at as a number (and such do not have a an actual period they are simply a number with a decimal value), the ones that have 2 periods are looked at as strings.

    To fix this change your criteria to the below (The JOIN function will convert it to a string and allow to look for a period)

    =COUNTIF(rID:rID, CONTAINS(".", JOIN(@cell)))

  • Bill in Ohio
    Bill in Ohio ✭✭✭
    edited 12/15/23

    Thank you @Leibel S , I am not sure why my post went through twice. I did get an error message submitting my question, but also received an answer on the other one as well. Such a great community!

    I was looking for each particular row though, so I wasn't able to get just the numbers of "." in that row. I'll get better though, and I'm sure that I might be able to expand on that in the future.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @Bill in Ohio

    Do you remember what the error message said? I'm noticing quite a few duplicate posts in the forum and I'm trying to replicate what's happening. Would appreciate your help! 🙂

  • Bill in Ohio

    No, and I didn't think to take a screenshot of it either, sorry. I will try to do that next time. All I remember was a 500:500 error, and that didn't mean much to me.

  • Genevieve P.
    Genevieve P. Employee Admin

    That's already really helpful, thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!