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

Find more posts tagged with

Accepted answers

Christian Graf

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)

All comments

Paul Newcome

~~What is the formula you are using to populate the WBS column? Did you try adding + "" to the end of that formula?~~

My apologies. I missed the last part of your post. Try using the "paste special" option when pasting the values in. You could also use the feature to adjust how many decimal points are displayed and then use the + "" method to convert it to a text string.

Christian Graf

I've tried the decimal Idea, It adds zeros to everything so that won't work.

Ex.

1.8 -> 1.80

1.9 -> 1.90

1.1 -> 1.10

1.11 -> 1.11

I'll try paste special, that might be exactly what I'm looking for on the data entry side.

Edit*

Paste Special didn't work.

Paul Newcome

I see now. Sorry about that. You need one point one and one point ten, but you are getting two of either one depending on the method. Ok.

There are a few different WBS solutions floating around here in the Community that should be able to accomplish what you need.

Christian Graf

@Paul Newcome

What is WBS?

Thanks for taking a stab at it.

Edit*

What I have right now is a conditional formatting that highlights all numerical values in that column so I go through manually and add an apostrophe in front of problem ones. I'm not sure I can add a formula, because the data will be changing positions when things are sorted.

Paul Newcome

Take a look through this thread. This should have a solution that will work for you.

https://community.smartsheet.com/discussion/77827/work-breakdown-structure-wbs-column-formula-configuration

Christian Graf

=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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.