Force a format in a cell

StephanieR · 03/03/21

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

Paul Newcome · 03/03/21

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.

StephanieR · 03/03/21

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

Paul Newcome · 03/03/21

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.

Andrée Starå · 03/03/21

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!

StephanieR · 03/03/21

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

StephanieR · 03/03/21

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 · 03/03/21

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.