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

palmcitytim

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.

Mary_A

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

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?