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

Options

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.

Tags:

Best Answer

Answers

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

    Hey @Bill in Ohio

    Try this

    =(LEN(rID@row) - LEN(SUBSTITUTE(rID@row, ".", ""))) / LEN(".")

    Will it work for you?

    Kelly

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    I believe while number entries and entries with one decimal are being treated as numbers, while entries with more than one decimal are treated as text.

  • Bill in Ohio
    Options

    Thank you so much @Kelly Moore ! I can definitely use that! Anywhere that = 0 are my base numbers.

    @Carson Penticuff - I do think that is one of the drawbacks of not being able to specify text or number specifically. In this case, and probably any versioning system, you would want text.

    Thank you both for the replies.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Bill in Ohio

    Glad to help. (And btw, I lived in OH for ~10yrs). In your example above I didn't need to include the LEN in the denominator since your LEN=1, but I wanted you to have the entire formula in the event your textstring changes. This formula will also work if you have a multiselect column and you're trying to get a count - you must first do a JOIN of that field to make the values one long textstring. This will make sense if you ever need to use the formula in that instance.

    Happy Holidays

    Kelly

  • Bill in Ohio
    Options

    Well thank you, it worked like a charm. I wouldn't have thought of using the LEN function, but it worked for all of them, and I was then able to check/uncheck another indicator column for filtering on Risks or mitigations.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!