Wildcard Email Address In Form

Options
Benjamin O'Leary
edited 06/14/23 in Smartsheet Basics

I currently have a form with a contact list field that allows multiple email addresses. Because of this, users can input values that are not email addresses. It is possible to validate a contact list field that allows multiple email addresses?

If you can't, is it possible to input a wildcard so that field requires the backend of an email address (@company.com)?

Ben

Tags:

Answers

  • Deric
    Deric ✭✭✭✭✭
    Options

    Are you aware that there is an email validation function in the form? I think this would be the cleanest way to make sure that you are only collecting email addresses.

    The second part of your questions is pretty easy to handle. The first part is possible, but not practical.

    To validate an email addess you can use something like this:

    =IFERROR(IF(RIGHT([Email]@row, 15) = "@smartsheet.com", [Email]@row, "Not Valid")), "")

    The number 15 in the RIGHT function refers to the number of characters in the tail of the verified email.

    Extracting multiple emails is possible, but it would require a lot of collumns to make it happen. You would have to use find the first @ sign in a string, use that to pull the first email address, in another column use the @ sign to get everything after the first email address. Then repeat for each email address. For each email address you would need two columns and you couldn't scale without adding more columns.

  • Benjamin O'Leary
    Options

    I am getting an #UNPARSEABLE error:

    =IFERROR(IF(RIGHT([Local Product Manager(s)]@row, 10) = "@roche.com", [Local Product Manager(s)]@row, "Not Valid")), "")

  • Deric
    Deric ✭✭✭✭✭
    Options

    @Benjamin O'Leary is your [Local Product Manager(s)] column a text field or a contact field? It needs to be a text field for this formula to work. The validation column (with the formula) can be a contact column.

  • Benjamin O'Leary
    Options

    @Deric I changed the column types, and it still is giving me an #UNPARSEABLE error:

    =IFERROR(IF(RIGHT([Local Product Manager]@row, 10) = "@roche.com", [Local Product Manager]@row, "Not Valid")), "")

  • Deric
    Deric ✭✭✭✭✭
    Options

    @Benjamin O'Leary I had too many close brackets. Sorry for the bad example.

    =IFERROR(IF(RIGHT([Local Product Manager]@row, 10) = "@roche.com", [Local Product Manager]@row, "Not Valid"), "")