I'm looking to count the number of periods in a string and add values based on that number.

Christian Graf

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

Nick Burrus

=LEN(Item@row) - LEN(SUBSTITUTE(Item@row, ".", ""))

Try that