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

Options

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

  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭
    Answer ✓
    Options

    =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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!