I'm looking to count the number of periods in a string and add values based on that number.
Here I have a hierarchy and it currently looks like this:
1
1.1
1.1.1
1.1.1.1
I need to modify it so that it will look like this:
1.0.0.0.0.0.0.0
1.1.0.0.0.0.0.0
1.1.1.0.0.0.0.0
1.1.1.1.0.0.0.0
I tried this equation in a column named [Item Hierarchy] which referenced a column named [Item]:
=IF(COUNTIF([Item]@row, CONTAINS(".", [Item]@row)) = 0, [Item]@row + ".0.0.0.0.0")
Thanks
Best Answer
-
=LEN(Item@row) - LEN(SUBSTITUTE(Item@row, ".", ""))
Try that
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
Answers
-
=LEN(Item@row) - LEN(SUBSTITUTE(Item@row, ".", ""))
Try that
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
-
That worked! thank you so much!
-
Damn, you're good, Nick. Just found this. Worked for me. Thanks.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!