Is there a way to convert a large number of contact cells at once?

I have over 5000 rows in a sheet that has been exported from ServiceNow. Every row has a contact cell that is filled out with a raw name, no company email. Is there a way of converting every cell into the respective company email for that person at once?

The manual method, typing their name and selecting the pop up, will take way too long.

Maybe there is a solution using the amount of unique contacts in a separate sheet? There are 290 at the moment in this sheet (the number of rows grows daily).

Any ideas to a solution would be greatly appreciated.


  • Mr. Chris
    Mr. Chris ✭✭✭✭✭

    Without seeing the data you're working with you can experiment with something like this:

    =SUBSTITUTE([Name Column]@row, " ", ".") + [Email Domain]@row

    This will take the name, substitute spaces " " with a "." then add the email domain.

    If the domain is always the same you can use this as well and do away with the Email Domain column:

    =SUBSTITUTE([Name Column]@row, " ", ".") + ""

    I just used the email domain column to for a visual here:

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!