Creating an email out of a full name

Hi,

I am trying to make an email out of a full name cell and am running into issues when trying the Right/Left method (ex: =RIGHT(A2,LEN(A2)-FIND(" ",A2,1)) and =LEFT(A2,SEARCH(" ",A2)).

When trying the above method, the Left would not work (was going to split them up and bring back together with CONCATENATE).

All emails on this sheet follow the same pattern: "First"."Last"@gmail.com like the example below. Is there a reason the =left function is not working? Is there a better method for Smartsheets?

Thanks for any input!

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Desertbird

Try this:

=LEFT(Name@row, FIND(" ", Name@row) - 1) + "." + RIGHT(Name@row, FIND(" ", Name@row)) + "@gmail.com"

You'll want to find the Space between the first and last name in the Name cell and use that (minus 1) as the starting position for the LEFT and RIGHT functions. Then you can add together the other elements of an email with the + symbol.

Let me know if this works!

Cheers,

Genevieve

Desertbird

Hi, Yes thank you this worked, however it left a space between the period and the last name, is there a way to remove this?

Genevieve P.

Hi @Desertbird

My apologies! We need to use something slightly different for the RIGHT portion of the formula. To find the character to start returning values from, we can use LEN to calculate the number of characters in the cell, then minus the number of characters from the left of the space.

Try this:

=LEFT(Name@row, FIND(" ", Name@row) - 1) + "." + RIGHT(Name@row, (LEN(Name@row) - FIND(" ", Name@row))) + "@gmail.com"

Cheers,

Genevieve

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.