Unable to insert a trailing space

Sanjay Cherubala

I would like the content of a cell have one or more trailing spaces like "test text ", but the UI seems to automatically strip out trailing spaces before saving. Any advice on hos I can retain trailing spaces is much appreciated!

Rob Hagan

Quick experiment of ="Test Text" + " " to attempt to trick SmartSheet into retaining the trailing space didn't work. Even a silly one like =SUBSTITUTE("Test Text!", "!", " ") returned "Test Text". Sorry, probably not. Over to larger minds...

J. Craig Williams

Sanjay,

 

Rob's analsys is correct.

 

Can I ask why you desire a trailing space? Perhaps there is a different way to solve the fundamental problem.

 

Craig

Sanjay Cherubala

I am using the sheet as a point of data entry and have a script (using the API) that exports information into a custom file format. In one case I have two columns that contain strings that need to be concatenated on export. I cannot automatically add spaces between the fields because that may not be what the author intended. 

 

I guess my question is why does Smartsheet strip the trailing space in the first place? Excel does not exhibit this behavior.

J. Craig Williams

I can't answer design questions with authority, though I often have opinions and guesses.

 

Instead of fixing it on the Smartsheet side, why not check if you have the both columns populated with data and if so, add the space, otherwise only the one with data?

 

Craig

Sanjay Cherubala

As mentioned in my previous comment in the thread, in this case, I can't assume the separator is a space. In one instance the separator is a hyphen and in another, a space.

J. Craig Williams

Is there a reaon for that separator? How does the person entering the data know if there a space or a hyphen? If they know, you might be able to glean that from the data in the row to make your programming choices.

 

Have you considered putting that separator in its own column?

 

=[ColumnA]1 + [Separator]1 + [ColumnB]1

 

A space won't show up, but Substitute could be used to replace "!" with " ".

Then you aren't worrying about a trailing space but rather the sepatator.

 

Just thinking out loud.

 

Craig

 

 

 

Sanjay Cherubala

That's a good suggestion, but in my case there are multiple columns in the same sheet that require concatenation and managing an extra column for each of these instance will get cumbersome. 

 

However, the idea of using a special character that gets substituted by a space upon export is something I will explore. I'm still curious about why Smartsheet does not retain spaces though - anyone from the company care to address that question?