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

Bill in Ohio

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

@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

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.

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.

That's already really helpful, thank you.