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.

Find more posts tagged with

Sheets

API and Developers

Formulas

Cell linking

Accepted answers

Genevieve P.

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

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

All comments

Genevieve P.

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.