Why can't I see zeroes?

Brandon Tarver

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?

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

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

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

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

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

 

Craig