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!