SharePoint Calculated Column Combine First Middle Last Name without Additional Spaces

[wskinnermctcsp]

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])))

• 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.

1. Willow Sarah Eastwood PHD
2. Willow Eastwood PHD
3. Willow Eastwood
4. Blank Space Middle Name: Willow _ Eastwood PHD
5. Blank Space in Middle and Suffix: Willow _ Eastwood _
6. Blank Spaces in All Names: _ _ _ _
7. All Names Not Filled Left Blank: blank blank blank blank
8. First Name Only: Willow blank blank blank
9. Last Name Only: blank blank Eastwood blank
10. 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?

[#2]

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.

CombineFormula Final Name Results

[#3]

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.

Name Examples Initial Basic Formula Results

[#4 · Accepted solution]

@wskinnermctcsp 

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

[#5]

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.