Possible bug: Smartsheet adds a trailing space to random cells

I've encountered some strange behavior where Smartsheet automatically adds a trailing space to a cell.

Below are two columns. The right column adds a "|" to the value in the left column. You can see that in the penultimate row a trailing space is added to the cell:

I am using the COLLECT function to show the contents of a column of sheet A in sheet B. Both sheets have a few columns in common. In some (but not all) of the cells, Smartsheet adds a trailing space, which breaks the COLLECT function.

Some additional information:

  • The values in the left column are added using the API, but I am sure that the input data does not include trailing spaces.
  • Double-clicking the cell removes the space (as is standard Smartsheet behavior) and resolves the issue.

Is there anyone else who has experience with this issue? Thanks in advance.

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @JKrombeenASK

    Will the values in Column A (coming in from the API) always have 5 characters?

    If so, we can use a helper column with a LEFT Function to only grab the 5 characters and strip out any possible spaces, like so:

    =LEFT([Column A]@row, 5)

    Cheers,

    Genevieve

  • JKrombeenASK
    Answer ✓

    Hi Genevieve,

    Thank you for your reply.

    I came up with a solution similar to the one you propose, but feasible for all value lengths:

    IF(RIGHT([VB]@row) = " "; LEFT([VB]@row; LEN([VB]@row) - 1); [VB]@row)

    The formula above checks if the final character is a space, and if so, removes the final character.


    As you point out, this does require a helper column. And of course it is more of a workaround solution to the actual problem: a bug in the way Smartsheet handles incoming data.


    Thank you for your time anyway!


    Jasper

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @JKrombeenASK

    Will the values in Column A (coming in from the API) always have 5 characters?

    If so, we can use a helper column with a LEFT Function to only grab the 5 characters and strip out any possible spaces, like so:

    =LEFT([Column A]@row, 5)

    Cheers,

    Genevieve

  • JKrombeenASK
    Answer ✓

    Hi Genevieve,

    Thank you for your reply.

    I came up with a solution similar to the one you propose, but feasible for all value lengths:

    IF(RIGHT([VB]@row) = " "; LEFT([VB]@row; LEN([VB]@row) - 1); [VB]@row)

    The formula above checks if the final character is a space, and if so, removes the final character.


    As you point out, this does require a helper column. And of course it is more of a workaround solution to the actual problem: a bug in the way Smartsheet handles incoming data.


    Thank you for your time anyway!


    Jasper