0

I have a user with a Document Library called Submissions. Below is an excel example of what the user's library looks like with data:

enter image description here

The Display Name column is a Calculated Column. The user wants/insists to have the column Display Name to output in a format like Doe, John instead of John.doe.

Below is the setting the user had when this request was brought to my attention:

enter image description here

I told the user it was impossible to output as requested due to it pulling from Employee column and formula is just impossible to do so. I need a second opinion/technical response to further explain why it cannot be done. On the other hand, if I'm wrong and there is a way to output the calculated column Display Name to Doe, John can someone please show me how to do it?

1

It is possible, but you might run into edge cases which you constantly have to solve

On first sight its:

  • get the string after the dot
  • Capitalize it
  • get the string before the dot
  • Capitalize it
  • join them with a comma

[I will now fire up my CalcMaster plugin to come up with a Formula]

Here goes

This is from my (private use only) Calculated Column editor

So all in one column:

=PROPER(MID(Employee,FIND(".",Employee)+1,255))
 &", "
 &Proper(MID(Employee,1,FIND(".",Employee)-1))

But you have to test for any edge cases against all your Employee names

FYI, You can built these formulas yourself in Excel and use Named Ranges for the fieldnames.
The merging of Columns (blue in my CalcMaster) you have to do by hand.

Only a subset of Excel Functions work in SharePoint, I tested all: https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

  • I didn't know it was possible. Could you please let me know what the formula is when you can? Thanks! – Aspire27 Oct 23 '15 at 17:50
  • Danny you are awesome. Hats off to you sir. Could you clarify what you mean when you say "edge cases" for which I constantly have to solve? Please advise. – Aspire27 Oct 23 '15 at 18:18
  • One edge case this Formula does not solve is Employee names with two periods. I can't think of others off the top of my head.. guess why they are called edge-cases – Danny '365CSI' Engelman Oct 23 '15 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.