Is there a formula to Upper case the first letter of a hyphenated name after the hyphen?
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.
Best Answers
-
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)))
Sincerely,
Jacob Stey
-
Answers
-
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))
Sincerely,
Jacob Stey
-
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)))
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
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.
-
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.
-
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 ----)
Sincerely,
Jacob Stey
-
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.
-
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)))
Sincerely,
Jacob Stey
-
@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!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!