Is it possible to query the email address field within the Contact field?

Options

We need to make sure the email address within the contact field (which has two parts: email address and firstlast) matches another field having the email address.

Answers

  • Mary_A
    Mary_A ✭✭✭✭✭✭
    edited 02/03/22
    Options

    We do something like this using Control Center --- so you would need to use something similar.


    Create a Contact column in Sheet A

    Link the Contact column to a Text column in the Profile Data sheet.

    When the Contact column value arrives in the Text destination it comes across in this format.

    First Last <user@company.com>

    In a helper column/field, place a formula that strips out email:

    =MID(Value$23, (FIND("<", Value$23) + 1), ((FIND(">", Value$23) - 1) - (FIND("<", Value$23))))

    Note: VALUE$23 is the Text destination location which is the same in each profile data sheet.

    The key seems to be the link but you may be able to do this with a copy.

    This gets you to the contact's email and you can now compare it either by expanding the formula complexity in the helper --- doing it in one go. Or creating a second helper that matches and returns true/false value.

    I played around with trying this in one sheet but couldn't quite get there. Perhaps another person can help you do this if you are not using Control Center.

  • palmcitytim
    palmcitytim ✭✭✭✭
    Options

    Thanks Mary, very helpful. Works great except for your 1st 3 instructions. It only comes over as first last. However, if I copy / Paste values then I see

    first last <email>

    Really don't want to settle for a manual copy paste. Any other ideas?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!