Force a format in a cell

We have a form that requires a 10 digit phone number. Clients are not entering the full number, is there a way to have Smartsheet make sure that 10 digits are entered in that cell?


Thank you,

Stephanie

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There is no way to force a format, but you can use a helper column to check whether or not the format is accurate and then send an alert/update request after the fact.


    If users are entering data directly into the sheet (as opposed to through a form), you can use the same helper column and set up conditional formatting to turn the cell red and even have a formula in the helper column that outputs text of "Please use format of (xxx) xxx-xxxx" right next to the phone number column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • StephanieR
    StephanieR ✭✭
    Answer ✓

    Hi Andree,

    This form is for pre-registering for Covid-19 vaccination, so we are getting a couple hundred entries a day. I can set-up a work flow, but what would work better....is if I could have Smartsheet email the client directly with a templated/boilerplate message, if the cell did NOT meet the requirements.

    I can create a workflow to send Phone column with less than 10 digits to "Bad Phone" sheet so we can email this group of people.

    As always you have been incredibly helpful!

    Stephanie

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There is no way to force a format, but you can use a helper column to check whether or not the format is accurate and then send an alert/update request after the fact.


    If users are entering data directly into the sheet (as opposed to through a form), you can use the same helper column and set up conditional formatting to turn the cell red and even have a formula in the helper column that outputs text of "Please use format of (xxx) xxx-xxxx" right next to the phone number column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @StephanieR

    I hope you're well and safe!

    Unfortunately, as far as I know, it's not possible now, but it's an excellent idea!

    Please submit an Enhancement Request when you have a moment.

    As a possible workaround, you could have a Workflow send an Update Request if the number isn't 10 digits.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • StephanieR
    StephanieR ✭✭
    Answer ✓

    Hi Andree,

    This form is for pre-registering for Covid-19 vaccination, so we are getting a couple hundred entries a day. I can set-up a work flow, but what would work better....is if I could have Smartsheet email the client directly with a templated/boilerplate message, if the cell did NOT meet the requirements.

    I can create a workflow to send Phone column with less than 10 digits to "Bad Phone" sheet so we can email this group of people.

    As always you have been incredibly helpful!

    Stephanie

  • Hi Paul,

    They are only allowed to enter through the form. I do have helper text, but at this time we are addressing seniors 65 and older. Most of the people using this form have an average age of 75+, although we do have a significant portion of much younger clients that can't enter their phone number correctly either!!

    Stephanie

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I was going to suggest that using a form you can add helper text, but it looks like you already have that set up.


    Would you be interested in some helper columns and formulas that will essentially "reformat" within the sheet after the entry has been made? I do know of another thread here in the community where this particular issue was solved. I can try to dig it up if you are interested.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @StephanieR

    You're more than welcome!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @StephanieR

    You can validate form fields for certain inputs.

    Text/number column validated to number, percent, email, phone

    Contact column validated for email.

    See more information here: https://help.smartsheet.com/articles/2482427