Is there a formula to Upper case the first letter of a hyphenated name after the hyphen?

Trang Turtletraxx

I have this formula to create a first and last name from an email address that is last then first name. Some of our employees have hyphenated first or last names, but it is not capitalizing the first letter after the hyphen.

=IF(ISBLANK([Submitter Email]@row), "", UPPER(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row, 1) + 1, 1)) + RIGHT([Submitter User Name]@row, LEN([Submitter User Name]@row) - FIND("_", [Submitter User Name]@row, 1) - 1) + " " + UPPER(LEFT([Submitter User Name]@row, 1)) + MID([Submitter User Name]@row, 2, FIND("_", [Submitter User Name]@row, 2) - 2))

Does anyone know a trick or formula to fix this? Some people are very irritated by their names not properly Upper cased. Thanks in advance.

SteyJ

https://community.smartsheet.com/discussion/comment/410572#Comment_410572

I had fun figuring this out.

Here you go.

=IF(CONTAINS("-", [Submitter User Name]@row) = 1, UPPER(LEFT([Submitter User Name]@row, 1)) + 
MID([Submitter User Name]@row, 2, FIND("_", [Submitter User Name]@row) - 2) + " " + 
UPPER(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row, 1) + 1, 1)) + 
IFERROR(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row) + 2, 
IF((FIND("-", [Submitter User Name]@row) - FIND("_", [Submitter User Name]@row) - 2) >= 0, 
FIND("-", [Submitter User Name]@row) - FIND("_", [Submitter User Name]@row) - 2)), "") + 
IFERROR(UPPER(MID([Submitter User Name]@row, FIND("-", [Submitter User Name]@row, 1), 2)), "") + 
MID([Submitter User Name]@row, FIND("-", [Submitter User Name]@row) + 2, 
LEN([Submitter User Name]@row) - FIND("-", [Submitter User Name]@row)), 
UPPER(LEFT([Submitter User Name]@row, 1)) + MID([Submitter User Name]@row, 2, FIND("_", [Submitter User Name]@row) - 2) + " " + 
UPPER(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row) + 1, 1)) + MID([Submitter User Name]@row, 
FIND("_", [Submitter User Name]@row) + 2, LEN([Submitter User Name]@row) - FIND("_", [Submitter User Name]@row)))

SteyJ

https://community.smartsheet.com/discussion/comment/410662#Comment_410662

Anytime 👍

SteyJ

https://community.smartsheet.com/discussion/114634/is-there-a-formula-to-upper-case-the-first-letter-of-a-hyphenated-name-after-the-hyphen

Try this:

=UPPER(LEFT([Submitter User Name]@row, 1)) + 
MID([Submitter User Name]@row, 2, VALUE(LEFT(FIND("_", [Submitter User Name]@row) - 2))) + " " + 
UPPER(MID([Submitter User Name]@row, VALUE(LEFT(FIND("_", [Submitter User Name]@row))) + 1, 1)) + 
MID([Submitter User Name]@row, VALUE(LEFT(FIND("_", [Submitter User Name]@row) + 2, +2)), 
LEN([Submitter User Name]@row))

Jason Albrecht

Hello @Trang Turtletraxx,

The following formula uses the REPLACE Function and works whether the name has a hyphen or not.

I'm not sure where or how you would use it in the formula you've provided until I understand the context (e.g., I'm assuming all your emails include an underscore "_"?).

=REPLACE([Submitter User Name]@row, FIND("-", [Submitter User Name]@row) + 1, 1, UPPER(LEFT(RIGHT([Submitter User Name]@row, LEN([Submitter User Name]@row) - FIND("-", [Submitter User Name]@row)), 1)))

Trang Turtletraxx

Thanks for the feedback. All emails are in the same format lastname_firstname@email.com with an underscore between the last and first names.

Example: Smith_Jane-Lyn@email.com

My formula displays the name as Jane-lyn Smith. The 2 suggested formulas are displaying as Smith Amy-lyn. Neither one capitalizes the name after the hyphen.

Trang Turtletraxx

I should also mention I have a helper column that removes the email extension. The formula I feeding off a cell where it is just lastname_firstname to display FirstName LastName.

SteyJ

https://community.smartsheet.com/discussion/comment/410525#Comment_410525

If it's only a few one offs, you can just create a new column for manual entry and then type their name how they want it to appear. It would be far easier.

=IF([NewColumn]@row <>"", [NewColumn]@row, --- enter formula here that applies to most users ----)

Trang Turtletraxx

I appreciate the suggestion. I don't know how many "one offs" there would/could be. I work for a company of 5,000+ employees. The idea is to minimize human manual intervention. Your suggestion would also require someone to monitor the intake sheet which isn't efficient or effective from a operational process perspective.

SteyJ

https://community.smartsheet.com/discussion/comment/410572#Comment_410572

I had fun figuring this out.

Here you go.

=IF(CONTAINS("-", [Submitter User Name]@row) = 1, UPPER(LEFT([Submitter User Name]@row, 1)) + 
MID([Submitter User Name]@row, 2, FIND("_", [Submitter User Name]@row) - 2) + " " + 
UPPER(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row, 1) + 1, 1)) + 
IFERROR(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row) + 2, 
IF((FIND("-", [Submitter User Name]@row) - FIND("_", [Submitter User Name]@row) - 2) >= 0, 
FIND("-", [Submitter User Name]@row) - FIND("_", [Submitter User Name]@row) - 2)), "") + 
IFERROR(UPPER(MID([Submitter User Name]@row, FIND("-", [Submitter User Name]@row, 1), 2)), "") + 
MID([Submitter User Name]@row, FIND("-", [Submitter User Name]@row) + 2, 
LEN([Submitter User Name]@row) - FIND("-", [Submitter User Name]@row)), 
UPPER(LEFT([Submitter User Name]@row, 1)) + MID([Submitter User Name]@row, 2, FIND("_", [Submitter User Name]@row) - 2) + " " + 
UPPER(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row) + 1, 1)) + MID([Submitter User Name]@row, 
FIND("_", [Submitter User Name]@row) + 2, LEN([Submitter User Name]@row) - FIND("_", [Submitter User Name]@row)))

Trang Turtletraxx

@SteyJ Thank you SO much! This works perfectly for my problem. I'm glad you had fun figuring this out. After hours of trying to figure it out myself, I'm glad I found someone who could actually provide a solid solution. You're the BEST!

SteyJ

https://community.smartsheet.com/discussion/comment/410662#Comment_410662

Anytime 👍