Converting email addresses with hyphenated First and/or Last Names

Trang Turtletraxx

I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one.

I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob Shmoe

I have a "Conversion" column taking the email address capitalizing the first character of the person's first and last name, keeping any first names with a hyphens.

IFERROR(IF(CONTAINS("-", Email@row) = 1, UPPER(LEFT(Email@row, 1)) + MID(Email@row, 2, FIND("_", Email@row) - 2) + " " + UPPER(MID(Email@row, FIND("_", Email@row, 1) + 1, 1)) + IFERROR(MID(Email@row, FIND("_", Email@row) + 2, IF((FIND("-", Email@row) - FIND("_", Email@row) - 2) >= 0, FIND("-", Email@row) - FIND("_", Email@row) - 2)), "") + IFERROR(UPPER(MID(Email@row, FIND("-", Email@row, 1), 2)), "") + MID(Email@row, FIND("-", Email@row) + 2, LEN(Email@row) - FIND("-", Email@row)), UPPER(LEFT(Email@row, 1)) + MID(Email@row, 2, FIND("_", Email@row) - 2) + " " + UPPER(MID(Email@row, FIND("_", Email@row) + 1, 1)) + MID(Email@row, FIND("_", Email@row) + 2, LEN(Email@row) - FIND("_", Email@row))), "")

Then removing everything after @ LEFT(Conversion@row, FIND("@", Conversion@row, 1) - 1)

Lastly, I flip things into FirstName LastName format

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

All this works great keeping hyphenated first names into FirstName LastName format.

Not sure if I've just been working on this ONE formula for days and my brain has just stopped thinking, but I can't seem to move onto hyphenated LAST names and emails with numbers after the section of the first name given to those with similar names (i.e., shmoe_joe2@company.com) so there aren't any duplicate email addresses. So I'm trying to get a formula converting an email address that will:

1. Remove @company.com and the underscore symbol _ between the first/last names
2. Converting the email so the second half of the email is FirstName and the first half of the email is the LastName
3. Capitalizing the first character of each part of the name
4. Keeping the hyphens in the first and/or last name (if there is one)
5. Removing any numbers

So taking shmoe-smith_joe-bob2@company.com and converting it into Joe-Bob Shmoe-Smith

Any help would be greatly appreciated.

Leibel S

@Trang Turtletraxx

I put this together assuming that, there is only 1 possible hyphen in first and last, the number possibilities are only 1 2 or 3 (you can add more if needed)

Add columns:

1. First Name: =UPPER(MID(Email@row, FIND("_", Email@row) + 1, 1)) + MID(Email@row, FIND("_", Email@row) + 2, FIND("@", Email@row) - FIND("_", Email@row) - 2)
2. First Name Hyphen Position: =FIND("-", [First Name]@row)
3. Last Name: =UPPER(LEFT(Email@row, 1)) + MID(Email@row, 2, FIND("_", Email@row) - 2)
4. Last Name Hyphen Position: =FIND("-", [Last Name]@row)
5. Final Result: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(CONTAINS("-", [First Name]@row), LEFT([First Name]@row, [First Name Hyphen Position]@row) + UPPER(MID([First Name]@row, [First Name Hyphen Position]@row + 1, 1)) + MID([First Name]@row, [First Name Hyphen Position]@row + 2, LEN([First Name]@row) - [First Name Hyphen Position]@row - 1), [First Name]@row) + " " + IF(CONTAINS("-", [Last Name]@row), LEFT([Last Name]@row, [Last Name Hyphen Position]@row) + UPPER(MID([Last Name]@row, [Last Name Hyphen Position]@row + 1, 1)) + MID([Last Name]@row, [Last Name Hyphen Position]@row + 2, LEN([Last Name]@row) - [Last Name Hyphen Position]@row - 1), [Last Name]@row), "1", ""), "2", ""), "3", "")

Leibel S

@Trang Turtletraxx

I put this together assuming that, there is only 1 possible hyphen in first and last, the number possibilities are only 1 2 or 3 (you can add more if needed)

Add columns:

1. First Name: =UPPER(MID(Email@row, FIND("_", Email@row) + 1, 1)) + MID(Email@row, FIND("_", Email@row) + 2, FIND("@", Email@row) - FIND("_", Email@row) - 2)
2. First Name Hyphen Position: =FIND("-", [First Name]@row)
3. Last Name: =UPPER(LEFT(Email@row, 1)) + MID(Email@row, 2, FIND("_", Email@row) - 2)
4. Last Name Hyphen Position: =FIND("-", [Last Name]@row)
5. Final Result: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(CONTAINS("-", [First Name]@row), LEFT([First Name]@row, [First Name Hyphen Position]@row) + UPPER(MID([First Name]@row, [First Name Hyphen Position]@row + 1, 1)) + MID([First Name]@row, [First Name Hyphen Position]@row + 2, LEN([First Name]@row) - [First Name Hyphen Position]@row - 1), [First Name]@row) + " " + IF(CONTAINS("-", [Last Name]@row), LEFT([Last Name]@row, [Last Name Hyphen Position]@row) + UPPER(MID([Last Name]@row, [Last Name Hyphen Position]@row + 1, 1)) + MID([Last Name]@row, [Last Name Hyphen Position]@row + 2, LEN([Last Name]@row) - [Last Name Hyphen Position]@row - 1), [Last Name]@row), "1", ""), "2", ""), "3", "")

Trang Turtletraxx

@Leibel S you are a genius. You simplified my process SO much. Thank you! Thank you! Thank you!

Paul Newcome

This one had me curious, so I had to play around with it a bit too. This is what I came up with. It is a single column formula and can handle numbers 0 - 9 before the "@". I'd have to play a little bit more, but I have ideas on how to accommodate 0 - 99 if needed.

=SUBSTITUTE(SUBSTITUTE(UPPER(MID(Email@row, FIND("_", Email@row) + 1, 1)) + MID(Email@row, FIND("_", Email@row) + 2, FIND("@", Email@row) - (FIND("_", Email@row) + 2)), IFERROR(VALUE(MID(Email@row, FIND("@", Email@row) - 1, 1)), " "), ""), IF(FIND("-", Email@row, FIND("_", Email@row)) > 0, MID(Email@row, FIND("-", Email@row, FIND("_", Email@row)), 2), " "), IF(FIND("-", Email@row, FIND("_", Email@row)) > 0, UPPER(MID(Email@row, FIND("-", Email@row, FIND("_", Email@row)), 2)), "")) + " " + UPPER(LEFT(Email@row)) + IFERROR(IF(FIND("-", Email@row) < FIND("_", Email@row), MID(Email@row, 2, FIND("-", Email@row) - 1) + UPPER(MID(Email@row, FIND("-", Email@row) + 1, 1)) + MID(Email@row, FIND("-", Email@row) + 2, FIND("_", Email@row) - (FIND("-", Email@row) + 2)), MID(Email@row, 2, FIND("_", Email@row) - 2)), MID(Email@row, 2, FIND("_", Email@row) - 2))

Trang Turtletraxx

@Paul Newcome This may work. I have yet to see an email for similar names with double digit numbers, although I supposed you never know. I mean how many John Smith are there, right? I appreciate you taking the time to tackle this issue. I always appreciate your feedback.

Paul Newcome

No worries. And my response wasn't to imply that @Leibel S's solution won't work. I was just curious about it and wanted to see if my take would be different or not.

I think really the biggest difference between the two solutions is how the numbers are handled is all.

Trang Turtletraxx

Totally understood. It's always good to have alternatives. I learn people's thought processes and points of views when reading the different formulas and how they solve for the same thing.

Leibel S

https://community.smartsheet.com/discussion/comment/466510#Comment_466510

Your solution looks great! I like the variability way of dealing with the second UPPER and the numbers, did not think of it. Very cool

Paul Newcome

@Trang Turtletraxx I agree. Seeing different ways to solve the same problem is always a fun thing for me for a number of reasons.

@Leibel S Thanks! I kind of slapped it together piece by piece. In all I put it together across 7 or 8 different columns and then copy/pasted it all together. Haha.

The main thing I wanted to try out was the IFERROR/VALUE piece for the SUBSTITUTE function to swap out the numbers. I had the idea for it a while back, but since I never had a use case for it, I ended up forgetting about it before I had a chance to test it.

I feel like using another IFERROR/VALUE combo with the same logic to pull the 2 characters before the "@" (iferror would be pulling just the one with it's own iferror to cancel it out if none) would be the most straightforward way of solving for 0 - 99.