I'm creating a flow that will generate new personnel folders using document sets in SharePoint. I can populate the individual name fields (First, Middle, Last, Suffix); however, I want to have columns that will contain the first and last name or full name. Since people can also create personnel folders manually, I wanted the full name columns to be calculated. So when creating a folder they only have to enter the First, Middle Last, Suffix and then the full name columns will automatically populate.
I'm having an issue formatting the SharePoint calculated column and want to make sure it is correct before I set this as a content type and use it on all the folders.
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])))
- The first trim will wrap the entire name 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]))
- ) Closing initial trim
Below is a screenshot of each type of test names, the character lengths, result of ISBLANK, and result of ISNULL.
Example Names Lengths and Blanks
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.
- Willow Sarah Eastwood PHD
- Willow Eastwood PHD
- Willow Eastwood
- Blank Space Middle Name: Willow _ Eastwood PHD
- Blank Space in Middle and Suffix: Willow _ Eastwood _
- Blank Spaces in All Names: _ _ _ _
- All Names Not Filled Left Blank: blank blank blank blank
- First Name Only: Willow blank blank blank
- Last Name Only: blank blank Eastwood blank
- Trailing spaces on names: Willow_ Sarah_ Eastwood_ PHD_
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?
