Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Why can't I see zeroes?

I've got a long formula that determines an IP address for a computer.  That formula should result in a formula for an IP address that looks like this:

 

10.20.12.32

 

However, when there's a zero entry, SmartSheet is omitting the zero, so the result looks like this:

 

10..12.32

 

I need to show the zero!  If I type a zero in a SmartSheet, the zero shows.  However, if I get a zero as a result of a formula, (for example, =value(0)) SmartSheet displays nothing at all.  It's as if the formula didn't exist!

 

How can I force SmartSheet to show me my zero entries, rather than just pretending they never existed?

Comments

  • Tim McCarthy
    Tim McCarthy ✭✭✭✭✭

    The value function has an argument that is text. Can you give an example of how your other numbers are returned?

  • Brandon Tarver
    edited 04/28/16

    Here are the particulars. 

     

    In the column Store I have a store number.  That number may be one to four digits in length.  The IP address I'm creating begins with 10, uses the first two Store number digits (if they exist), uses the last two digits of the store number, and then ends with 32.  Leading zeroes are dropped.

     

    So, for example, store 2012 would have IP address 10.20.12.32.  Store 401 should have the IP address 10.4.1.32.  Store 1 would have the address 10.0.1.32.

     

    Here's my formula:  
    ="10." + VALUE(LEFT(Store1, MAX(0, LEN(Store1) - 2))) + "." + VALUE(RIGHT(Store1, 2)) + ".32"

     

    I apprecaite your willingness to look at this.  To me, it appears that SmartSheet just isn't handling the zero as a standalone spreadsheet would.

     

  • Tim McCarthy
    Tim McCarthy ✭✭✭✭✭

    My best idea is that the store number must show at least 3 characters. The VALUE(LEFT(Store1, MAX(0, LEN(Store1) - 2))) is not defined if there are not enough characters in the string. So, for any store number less than three digits, you would need a text string "00#". Somehow you need to convert the store number to a text string.

     

    Also, the value of LEFT(Store1,0) can not be defined -- the leftmost zero characters in the string?.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I think Tim is on the right track.

     

    VALUE(LEFT(Store1, MAX(0, LEN(Store1) - 2))) 

     

    should be

     

    VALUE(IF(LEN(Store1) > 2, LEFT(Store1, MAX(0, LEN(Store1) - 2)), "0"))

     

    So the whole formula becomes

     

    ="10." + VALUE(IF(LEN(Store1) > 2, LEFT(Store1, MAX(0, LEN(Store1) - 2)), "0")) + "." + VALUE(RIGHT(Store1, 2)) + ".32"

     

    Craig

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 04/29/16

    I'm not sure you need the VALUE function, but it might be a wash.

     

    Craig

This discussion has been closed.