Is there a function or formula to correct 1.1 hierarchy columns from being number and string values?

In this column I have highlighted the problem area. The smart sheet makes this into a decimal when there is one period, and makes removed the zero, breaking my hierarchy. I used this function in a helper column = Item@row + "" to make it into a string, but I'm left with the highlighted hierarchy as 1.1 instead of the required 1.10

Is there any way to lock the column type to only string data to fix the issue on import?

I need this to work because I will be using these hierarchies to determine which parent assembly the part is attached to. The data is Ctrl C (copied) from excel and Ctrl V pasted into smart sheet. Rather than make a helper excel file, I would prefer to have everything done in smart sheet so as to not complicate the process for the end user.


Thanks

Best Answer

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Answer ✓

    I was able to make an auto number column that I could tie into an equation for it to look back for multiples of the hierarchy, and if one existed behind it, and the project numbers matched, add a zero, else just keep the value.

    =IF(MIN(COLLECT(Item:Item, [SORT ID]:[SORT ID], <[SORT ID]@row, [EP Number]:[EP Number], =[EP Number]@row, Item:Item, =Item@row)) = Item@row, Item@row + "0", Item@row)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!