Formula - convert a name to e-mail address

Jef Snyders

Hi all,

I am looking to convert a name to a e-mail address using a formula.

This is the example: convert 'Andreas Johnson' to '[email protected]'

Carson Penticuff

This will work for instances where there is a single first and single last name, separated by a space. If you get into more complicated names, things get harder to work out.

=IF([Name]@row <> "", LEFT([Name]@row, FIND(" ", [Name]@row) - 1) + "." + RIGHT([Name]@row, LEN([Name]@row) - FIND(" ", [Name]@row)) + "@company.net", "")

Carson Penticuff

This will work for instances where there is a single first and single last name, separated by a space. If you get into more complicated names, things get harder to work out.

=IF([Name]@row <> "", LEFT([Name]@row, FIND(" ", [Name]@row) - 1) + "." + RIGHT([Name]@row, LEN([Name]@row) - FIND(" ", [Name]@row)) + "@company.net", "")

Jef Snyders

Hi Carson, You're a MASTER!

Don't understand of the formula, but it is exactly working as it should! Thanks so much!

Carson Penticuff

IF([Name]@row <> ""

This part of the formula basically just checks to see there is data in the cell. <> "" is just another way to say "something other than blank.

LEFT([Name]@row, 1) would take just the left "1" character from the cell. We determine how many characters we need to take from the left to get the first name by looking for the space between the names.

LEFT([Name]@row, FIND(" ", [Name]@row) - 1)

By adding the FIND() condition, we are looking to see where in the cell the space occurs. We then subtract 1 from the space's location to get the number of characters from the left side of the column to get the first name.

RIGHT([Name]@row, LEN([Name]@row) - FIND(" ", [Name]@row))

RIGHT() works similar to left. We want to take the right X number of characters. To determine how many characters we need here, we take the LEN() (length) of the total entry and subtract the location of the space.

The + character is a join. The overall idea of the formula is:

Take all the characters left of the space, add a period, then add all the characters to the right of the space, then add @company.net.

Jef Snyders

IF([Name]@row <> ""<\/p>

This part of the formula basically just checks to see there is data in the cell. <> "" is just another way to say "something other than blank.<\/p>

LEFT([Name]@row, 1) would take just the left "1" character from the cell. We determine how many characters we need to take from the left to get the first name by looking for the space between the names.<\/p>

LEFT([Name]@row, FIND(" ", [Name]@row) - 1)<\/p>

By adding the FIND() condition, we are looking to see where in the cell the space occurs. We then subtract 1 from the space's location to get the number of characters from the left side of the column to get the first name.<\/p>

RIGHT([Name]@row, LEN([Name]@row) - FIND(" ", [Name]@row))<\/p>

RIGHT() works similar to left. We want to take the right X number of characters. To determine how many characters we need here, we take the LEN() (length) of the total entry and subtract the location of the space. <\/p>

The + character is a join. The overall idea of the formula is:<\/p>

Take all the characters left of the space, add a period, then add all the characters to the right of the space, then add @company.net.<\/p>","bodyRaw":"[{\"insert\":\"IF([Name]@row <> \\\"\\\"\\nThis part of the formula basically just checks to see there is data in the cell. <> \\\"\\\" is just another way to say \\\"something other than blank.\\nLEFT([Name]@row, 1) would take just the left \\\"1\\\" character from the cell. We determine how many characters we need to take from the left to get the first name by looking for the space between the names.\\nLEFT([Name]@row, FIND(\\\" \\\", [Name]@row) - 1)\\nBy adding the FIND() condition, we are looking to see where in the cell the space occurs. We then subtract 1 from the space's location to get the number of characters from the left side of the column to get the first name.\\nRIGHT([Name]@row, LEN([Name]@row) - FIND(\\\" \\\", [Name]@row))\\nRIGHT() works similar to left. We want to take the right X number of characters. To determine how many characters we need here, we take the LEN() (length) of the total entry and subtract the location of the space. \\nThe + character is a join. The overall idea of the formula is:\\nTake all the characters left of the space, add a period, then add all the characters to the right of the space, then add @company.net.\\n\"}]","format":"rich","dateInserted":"2023-08-25T13:20:38+00:00","insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392560#Comment_392560","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/392560#Comment_392560

Hi Carson, you explained this so well. I now understand how this formula is built.

Don't think I have all understanding to create all formulas myself yet, but your answers give a lot of new insights. Thanks, thanks, thanks!