• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional format rows based on hierarchy structure without using VBA

I'm using WBS and have the hierarchy in Column A. Ex: 1.1, 1.1.1, 1.1.1.1, etc.
I need to apply a conditional format based on the number of periods/decimals in the number. Once I figure out the formula, I know how to set up the formatting for the indents. It's the formula that is puzzling me. Is there a formula that will count the number of periods/decimals? I've tried several things and nothing's working.
1.1 = indent 1 place
1.1.1 = indent 2 places
1.1.1.1 = indent 3 places

I was able to figure out the formula for 1.1 = indent 1 place. I have: =MOD($A1,1)>0. Works great, but need a formula for the numbers with more than one decimal.

I can not use VBA.
Thank you!
 
Suppose you data in celll A1
Try this formula
Note : Array Formula (Ctrl+Shit +Enter)
Code:
=IF(A1="","",SUMPRODUCT(IF(MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(TRIM(A1)))),1)=".",1,0)))
62556
 
Back
Top