I am struggling to create a formula to format a name, in a field that contains a first name and middle initial (SALLY S). I would like it to return Sally S or Sally.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

The data is currently in the field as all capital letters and does not always contain a middle initial. I am able to get the first name formatted, but am struggling with the middle initial. I feel like this should be simple, but I just can't get a formula to do what I need. I either want the middle initial to be capitalized as well or it could simply be cut out of the field.

Photo of Kelly

Kelly

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
Let's say the original text field is called [First Name + Middle - All Caps] - with the First Name, optional Middle Initial, all in caps.

You can use the following formula in a Formula Text field:

var text FirstNameAllCaps = Part([First Name + Middle - All Caps], 1, " ");

var text FirstNameTitleCase = Left($FirstNameAllCaps, 1) & Lower (Right($FirstNameAllCaps, Length($FirstNameAllCaps) -1) );


var text MiddleInitial = Upper ( Part([First Name + Middle - All Caps], 2, " ") );


List (" ", $FirstNameTitleCase, $MiddleInitial)




First, we use the Part function to get the FirstNameAllCaps - the part in the field before the first space, which would be the First Name in all caps.

Then we format it by concatentating the upper case first initial, with the lowercase version of the rest of the first name - to get FirstNameTitleCase.

Next - we use the Part function to get MiddleInitial - the middle initial, still capitalized.  If there's a chance the middle initial is not capitalized in the data, we use the Upper function to make sure it's capitalized as well.

Finally - we use the List function to put the FirstNameTitleCase and MiddleInitial together.  If there is no Middle Initial, the List function will display only FirstNameTitleCase, with no extra space at the end.
Photo of Kelly

Kelly

  • 0 Points
Thank you so much! This worked perfectly and I don't think I ever would have figured it out.
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
You're welcome - glad it helped!