0

Is there an established hierarchy for formulas in calculated columns?

Long story short our IT team got lazy and didn't complete user profiles.

In order to get a proper name from a user field I'm using "extract substring from index of string" in SPD. That value gives me "first name.last name" which I then have to use PROPER on to get "First name.Last name". I'm then disassembling both Firstname and Lastname using RIGHT and LEFT in two different columns with the "." as the delimiter and finally using CONCATENATE to put the two back together again.

5 columns to just get "Firstname Lastname".

I'd like to whittle that all down to one calculated column but I have no idea what formula should take precedence.

e.g. =CONCATENATE(Proper(Right)(Left))?

I wouldn't care about just using five columns to get what I want but for some reason unknown to me the calculated columns have un-hidden themselves and are showing up in the new form.

Improve this question
3
  • Can you post a sample of your user field data that you're using to extract Firstname Lastname? I think, you can extract the user name and put into a SP calculated column without additional columns that you have indicated.
    – Matiur Rahman
    Commented Dec 30, 2020 at 18:22
  • i:0#.w|aaa\first.last - I'm using extract substring to cut it down to first.last. I know I can concatenate the right / left formulas
    – David Hollowood
    Commented Dec 30, 2020 at 18:42
  • Good that you've already found the right formula to do the job.
    – Matiur Rahman
    Commented Dec 30, 2020 at 19:36

2 Answers 2

Reset to default
1

Answer: You cannot use

=CONCATENATE(Proper(Right)(Left))

The proper sequence or hierarchy is

CONCATENATE((PROPER(Left)),(PROPER(Right)))

In order to change a field with a value of first.last and make the value a proper name I used the following formula in a calculated column:

=CONCATENATE(PROPER(LEFT(fieldName,INT(FIND(".",fieldName)-1)))," ",PROPER(RIGHT(fieldName,LEN(fieldName)-INT(FIND(".",fieldName)))))

I hope this clarifies this satisfactorily.

Improve this answer
1
  • Hi David, Did you submit this formula as an answer to your question? or It's an update? Please if it's an answer to your question, so it would be great to add more details to clarify your answer to be helpful for future users. then Mark it as an answer. Thanks!
    – Mohamed El-Qassas MVP ♦
    Commented Dec 30, 2020 at 23:26
0

If you are using SharePoint designer workflow 2013 then you can use Replace Substring in String action to replace "." with space and save it in the list.

No need to use any calculated column for this.

Microsoft documentation: Workflow actions quick reference (SharePoint Workflow platform) - Utility actions

Example: SharePoint Designer 2013: Replace Substring in String Action

Improve this answer
3
  • I'm stuck with SPD 2010
    – David Hollowood
    Commented Dec 30, 2020 at 18:48
  • Which version of SharePoint you are using?
    – Ganesh Sanap - MVP
    Commented Dec 30, 2020 at 18:49
  • Modified SP2010. They never did an upgrade to 2013 or anything else.
    – David Hollowood
    Commented Dec 30, 2020 at 18:53

Your Answer

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

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