Spaces being stripped from sheet after input from form

Jack Parry

Any help with this one please

Jeff Reisman

@Jack Parry

I am seeing the same behavior. Since Smartsheet believes that's a number value, it's going to treat the space like a typo, like it doesn't exist, and enter the value as a number. To get it to not do this, you would need to make Smartsheet understand that this is text. Your options for doing that are:

Enter the value in the form with a leading apostrophe (this is the most straightforward.) Alternatively, you could try these other things.

Use a helper column to collect this number value, and:

Add a leading zero to the first number; or

Add a placeholder like an underscore or alpha character between the numbers.

On the sheet, in the GL1 column, use a SUBSTITUTE formula to replace the underscore or alpha character with a space:

=SUBSTITUTE([Helper Column]@row, "_", " ")

Or, if you went with the leading zero, modify the SUBSTITITE formula as follows:

=SUBSTITUTE([Helper Column]@row, "0", "", 1)

This tells it to replace the 0 with nothing, but only the first instance of 0 it finds, going from left to right.

Paul Newcome

Is it also happening for the General Comments field or just GL1?

Jack Parry

@Paul Newcome Hi Paul, just the GL1 field

Jeff Reisman

@Jack Parry

I am seeing the same behavior. Since Smartsheet believes that's a number value, it's going to treat the space like a typo, like it doesn't exist, and enter the value as a number. To get it to not do this, you would need to make Smartsheet understand that this is text. Your options for doing that are:

Enter the value in the form with a leading apostrophe (this is the most straightforward.) Alternatively, you could try these other things.

Use a helper column to collect this number value, and:

Add a leading zero to the first number; or

Add a placeholder like an underscore or alpha character between the numbers.

On the sheet, in the GL1 column, use a SUBSTITUTE formula to replace the underscore or alpha character with a space:

=SUBSTITUTE([Helper Column]@row, "_", " ")

Or, if you went with the leading zero, modify the SUBSTITITE formula as follows:

=SUBSTITUTE([Helper Column]@row, "0", "", 1)

This tells it to replace the 0 with nothing, but only the first instance of 0 it finds, going from left to right.

Jack Parry

@Jeff Reisman

thanks Jeff, is there a formula I can use to enter the leading zero or placeholder Or will I need to instruct the Form User to input these values?

Thanks,

Jack

Jeff Reisman

Unfortunately there's not yet the ability to specify leading characters or validate that specific of a format. You can add a description under the field header on the form telling them what to do.

young

Use the code "%20"

For example,

=[Form Link]@row + "?" + "Name"+SUBSTITUTE(Description@row, " ", "%20")

This solution helped me by the Pro-desk.