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?

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
3
  • I didn't know it was possible. Could you please let me know what the formula is when you can? Thanks!
    – Nina G
    Oct 23, 2015 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.
    – Nina G
    Oct 23, 2015 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, 2015 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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