Aug 19 2022 08:25 AM
Aug 19 2022 08:25 AM
Hello, I am currently working on making columns that will be used in a document set. When a new folder is created they will have to input the names (First, Middle, Last, Suffix). The document set columns will carry over to the files, so I wanted to make a calculated column that would combine the names so that it would be easier to filter or search.
I found plenty of examples which were merging only the first and last name exactly like the Microsoft Support Examples of Common Formulas ; but I could not find any examples where the middle name was included accounting for if someone doesn’t have a middle name.
If the person has a first and last name without the middle and the formula [FIRST_NAME]&” “&[MIDDLE_NAME]&” “&[LAST_NAME] would result in double spaces in between the first and last name.
I also need to account for input error where a space is included before or after the name, or a space is the only value in the field. For example when the name is entered into the fields, instead of putting a middle name or leaving blank they type a space. This would cause the field to not be blank and if using the standard merge calculation there would now be 3 spaces between first and last name.
I went through trial and error to find the formula that would work on a test sharepoint. My end result is a long formula checking for ISBlank while trimming any additional spaces and adding a space between names. The only way I could finally get it to work is by wrapping the entire formula in a TRIM() so that any additional spaces added by the merge would be removed.
Basically it checks if the field is blank, if so then add a space " ", if not then add the FieldName with a space in front, ex. " "&TRIM(FieldName).
My final formula:
TRIM(TRIM([FIRST_NAME])&IF(ISBLANK(TRIM([MIDDLE_NAME]))," "," "&TRIM([MIDDLE_NAME]))&IF(ISBLANK(TRIM([LAST_NAME]))," "," "&TRIM([LAST_NAME]))&IF(ISBLANK(TRIM([NAME_SUFF]))," "," "&TRIM([NAME_SUFF])))
Below is a screenshot of each type of test names, the character lengths, result of ISBLANK, and result of ISNULL.
Below are the names from the example. I'm using an underscore _ to represent spaces " " in field names. Blanks are when there is no value entered in the field.
Is there an easier way than a long validation formula? Is there a reason I can't find more examples of this issue? Are there name inputs that would cause errors or #Value from the formula?
Aug 19 2022 08:47 AM
Here is the results after making the final formula calculated column CombineFormula. I was using the column "Double Space in Combine Formula" to see if there were any additional spaces between words or within the final CombineFormula. It also checked the length and if it was blank. I don't understand why the zero 0 length final don't count as ISBLANK.
Aug 19 2022 09:06 AM
For reference here is the initial test which I just used the basic Text&" "&Text formula as used in the microsoft examples and other general name combine examples I found. It basically adds additional spaces and only works if every name field is filled in correctly.
=[FIRST_NAME]&" "&[MIDDLE_NAME]&" "&[LAST_NAME]&" "&[NAME_SUFF]
These are the results in SharePoint and Excel. You can see the additional spaces better in excel names.
Aug 20 2022 02:39 AMSolution
You should try below formula which will not add additional space if any field don't have value:
=CONCATENATE(TRIM(FIRST_NAME),IF(ISBLANK(TRIM(MIDDLE_NAME)),""," "),TRIM(MIDDLE_NAME),IF(ISBLANK(TRIM(LAST_NAME)),""," "),TRIM(LAST_NAME),IF(ISBLANK(TRIM(NAME_SUFF)),""," "),TRIM(NAME_SUFF))
For more function about calculation field in SharePoint visit Calculated Field Formulas
Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community
Aug 25 2022 07:21 AM
Thanks @kalpeshvaghela your formula works just as well. I'm marking your response as best because it is a little easier to read logically than mine.