Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭✭✭✭✭
    in Formulas and Functions

    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.

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • Community Champion
      Answer ✓
      Trang Turtletraxx

      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

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓
      Trang Turtletraxx

      Anytime 👍

      Sincerely,

      Jacob Stey

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion

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

      Trang Turtletraxx

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      edited 12/20/23

      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 "_"?).

      image.png


      =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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 12/20/23
      Trang Turtletraxx

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓
      Trang Turtletraxx

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @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!

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓
      Trang Turtletraxx

      Anytime 👍

      Sincerely,

      Jacob Stey

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions