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
-
The value function has an argument that is text. Can you give an example of how your other numbers are returned?
-
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.
-
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?.
-
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
-
I'm not sure you need the VALUE function, but it might be a wash.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives