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.

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey @Bill in Ohio

Try this

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

Will it work for you?

Kelly

All comments

Kelly Moore

Hey @Bill in Ohio

Try this

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

Will it work for you?

Kelly

Carson Penticuff

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

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

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.