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.
Best Answer
-
Hey @Bill in Ohio
Try this
=(LEN(rID@row) - LEN(SUBSTITUTE(rID@row, ".", ""))) / LEN(".")
Will it work for you?
Kelly
Answers
-
Hey @Bill in Ohio
Try this
=(LEN(rID@row) - LEN(SUBSTITUTE(rID@row, ".", ""))) / LEN(".")
Will it work for you?
Kelly
-
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.
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!