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

Options

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

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    Answer βœ“
    Options

    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
    SteyJ ✭✭✭✭✭✭
    Answer βœ“
    Options

Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    Options

    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

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    edited 12/20/23
    Options

    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

  • Trang Turtletraxx
    Trang Turtletraxx ✭✭✭
    Options

    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
    Trang Turtletraxx ✭✭✭
    Options

    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
    SteyJ ✭✭✭✭✭✭
    edited 12/20/23
    Options

    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

  • Trang Turtletraxx
    Trang Turtletraxx ✭✭✭
    Options

    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
    SteyJ ✭✭✭✭✭✭
    Answer βœ“
    Options

    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

  • Trang Turtletraxx
    Trang Turtletraxx ✭✭✭
    Options

    @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
    SteyJ ✭✭✭✭✭✭
    Answer βœ“
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!