Why does Smartsheet add a single quote before an integer that begins with 0?
A single quote keeps getting added when a 0 precedes an integer regardless of the entry method for the cell’s value (e.g., when the values are imported into Smartsheet from an Excel file, when the values are copied and pasted into Smartsheet or when the values are manually entered).
Example: 099999999 changes to ‘99999999
Why does this happen and how do we prevent this from happening? When this happens, it also messes up A to Z sorting.
There are also some instances where imported integers without leading zeroes have a single quote added by Smartsheet.
Answers
-
The ' is smartsheet's way of converting a numerical entry into text. If the ' was not there the data would be treated as a number and the leading 0 would be lost. Adding the ' means the data is treated as a text string, so the leading 0 is retained.
You can remove the ' by converting the data into a number using =VALUE(). This will, however, remove the leading 0 as well.
