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.

Find more posts tagged with

Sheets

Contacts

Formulas

Automations

Cell linking

Accepted answers

All comments

=Chris Palmer

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, " ", ".") + "@company.com"

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

unknown

This content has been removed.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.