# Hierarchy of Formulas In Calculated Columns

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.

• 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. 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 Dec 30, 2020 at 18:42
• Good that you've already found the right formula to do the job. Dec 30, 2020 at 19:36

``````=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.

• 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! Dec 30, 2020 at 23:26

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

• I'm stuck with SPD 2010 Dec 30, 2020 at 18:48
• Which version of SharePoint you are using? Dec 30, 2020 at 18:49
• Modified SP2010. They never did an upgrade to 2013 or anything else. Dec 30, 2020 at 18:53