Spaces being stripped from sheet after input from form

Jack Parry
Jack Parry ✭✭✭✭
edited 06/17/22 in Smartsheet Basics

Any help with this one please

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 06/17/22 Answer ✓

    @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.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!