Prefix with *

WWARRIOR2018

A Very happy New Year to everyone on here !

I have an interesting one which I am sure has been asked before but I cannot locate the answer

Were using a form for clients to enter the serial number of there devices in, however after typing the number in on the form, the results on the sheet are different so example is if client types in 3050.000977 then the form shows as 3050.00098 and causes major headaches at our office.

I know the workaround would be to enter a * before the serial number however asking a client to do this on the form I cant see them doing.

I was trying to avoid having a "helper column" which would append the value with the * so was wondering if anyone had any ideas

Find more posts tagged with

Accepted answers

All comments

ker9

@WWARRIOR2018

You can try using a helper column that will round down the number as follows:

=ROUNDDOWN([Your Column]@row, 5)

AKnight

Hi @WWARRIOR2018!

Smartsheet unfortunately is limited to 5 decimal places when the text is identified as a number. The reason why adding an asterisk fixes it is because it changes the cell type to text, rather than a number, and bypasses the decimal limitation. If you don't want a helper column, could you split the question to serial number prior to decimal (example 0000.XXXX) and remaining serial number after decimal (example XXXX.0000 - only put the numbers present in 0 locations)? The best solution is removing the decimal entirely from the text.

WWARRIOR2018

https://community.smartsheet.com/discussion/comment/467084#Comment_467084

Ker

Thanks for all your help, I think will have to use a helper collumn however when I use the above forumla its missing the last character, so serial 3050.000977 ends up being 3050.00097 any ideas ?

ker9

@WWARRIOR2018 - unfortunately, Smartsheet will only round up to five decimal places and you have 6 (sorry I missed that).

I think you may need to resort to two fields as @AKnight suggested.

Prefix (3050) and Suffix (000977) (pre-decimal/post-decimal), then join them with a formula if necessary.

WWARRIOR2018

Thanks for explaining about the limitation with decimals

My thoughts are using a helper collum which will copy the number to another collum and add a * infront of it which will then display the full number.

Is there a simple formula for this ?

WWARRIOR2018

So I setup a extra "helper Colum" with the formula ="*" + [SERIAL]@row

I was expecting that if the serial 3050.003797 was entered then the new helper column would show it correctly however it shows as *3050.0038

Any clues why its wrong as I thought adding the prefix would stop the decimal issue ?

ker9

@WWARRIOR2018

Presuming you use TWO columns - one for each prefix and suffix:

=LEFT(Prefix@row, 5) + "." + RIGHT(Suffix@row, 6)

I'm not aware of another method because Smartsheet rounds the number if entered as the complete number in one cell.

Your user form may not need to contain the decimal.

Hope this helps!

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.