SharePoint Calculated Column Combine First Middle Last Name Without Additional Spaces

[wskinnermctc]

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.

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?

[wskinnermctc]

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.

I had to test different formulas so I added columns the SP List and replaced the test formulas each time. The sharepoint column CombineFormula is where the results of the name joins. I used the column "Double Space in Combine Formula" to check for any additional spaces between words within the final CombineFormula. I also added columns to check the length and see if it was blank. 

=[FIRST_NAME]&" "&[MIDDLE_NAME]&" "&[LAST_NAME]&" "&[NAME_SUFF]

These are the results in SharePoint and Excel. You can see the additional spaces better in the excel names.

Name Examples Initial Basic Formula Results

[wskinnermctc]

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.

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

CombineFormula Final Name Results

[wskinnermctc · Accepted solution]

I also posted this on SharePoint Tech Community  and got a response for a solution from another user @kalpeshvaghela  that works just as well. I think his is a little easier to read than the one I have so I will mark it as a solution.

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

Easier to read solution formula:

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

These formulas are basically the same, but using Concatenate helps clean it up to be easier to understand at first look.

[Nate_willy]

Hello,

Thank you for this solution. I am attempting to make a calculated column in excel to combine multiple address entries into one column. I deal with military bases, so in the MS Form, I have branched around housing areas on the bases depending on the base that they choose. It's nice and customer friendly for a customer to select a base and then see the housing areas only for that base that they chose rather than all of them, but it has left a lot of blanks in my SharePoint List due to branching in the form. I would like to consolidate the address into one column, and I have applied the formulas you listed, but I cannot seem to get it to work. I keep getting a Syntax error not supported message when I finish. I've tried both solutions now and I keep getting the same. I hope this posts ok, it's my first time posting here. Below is what I put in the calculation field though:

=CONCATENATE(TRIM(Base),IF(ISBLANK(TRIM(Courtney Housing)),""," "),TRIM(Courtney Housing),IF(ISBLANK(TRIM(Foster Housing)),""," "),TRIM(Foster Housing),IF(ISBLANK(TRIM(Kadena Housing)),""," "),TRIM(Kadena Housing),IF(ISBLANK(TRIM(Lester Housing)),""," ")TRIM(Lester Housing),IF(ISBLANK(TRIM(McT Housing)),""," "),TRIM(McT Housing),IF(ISBLANK(TRIM(Shields Housing)),""," "),TRIM(Shields Housing),IF(ISBLANK(TRIM(Kinser Housing)),""," "),TRIM(Kinser Housing))

=TRIM(TRIM([Base])&IF(ISBLANK(TRIM([Courtney Housing])),""," "),TRIM([Courtney Housing])&IF(ISBLANK(TRIM([Foster Housing])),""," "),TRIM([Foster Housing])&IF(ISBLANK(TRIM([Kadena Housing])),""," "),TRIM([Kadena Housing])&IF(ISBLANK(TRIM([Lester Housing])),""," ")TRIM([Lester Housing])&IF(ISBLANK(TRIM([McT Housing])),""," "),TRIM([McT Housing])&IF(ISBLANK(TRIM([Shields Housing])),""," "),TRIM([Shields Housing])&IF(ISBLANK(TRIM([Kinser Housing])),""," "),TRIM([Kinser Housing]))

Any chance of an assist in finding the error? I have tried both formulas with and without square brackets around the other column titles as well.

Respectfully,

Nate_willy

[wskinnermctc]

The first thing I would check is the column internal names. It can be different than what is shown on the display name. There has also been a change in how SharePoint deals with spaces in column names. So you need to go check and see what it shows for your column internal name and use that in the formula.

I'm guessing that if you created the column named "Courtney Housing", the sharepoint internal column name is "CourtneyHousing" without a space in the middle. It used to replace spaces with _x0020_ and the internal column name would be "Courtney_x0020_Housing". 

In my example below I created a new column called "My - New_Column Name". The sharepoint internal column name can be seen in the address bar as "My_x002d_New_ColumnName". This is because it removed the spaces and encoded the special character dash "-" as _x002d_ in the name. 

Note: If I was to rename this column to say "My Favorite Column", the internal column name does not change and would still be addressed as "My_x002d_New_ColumnName". The internal column name doesn't change if you rename the column. That is considered changing the display name.

So go to your column settings and look at the internal column name for each of your columns, and then use that in your formula and see if it makes a difference.

SharePoint Column Name

[wskinnermctc]

Follow up question for you @Nate_willy about the data from the MS Form to SharePoint. If the SharePoint list is just a collector of responses and you don't expect any changes to the data, you could do all of this formatting in Power Automate.

It is essentially the same output, except you don't have to deal with a calculated column in SharePoint. You do a similar concatenate and trim formula in a Power Automate expression and it puts the whole thing as text into a SharePoint column.

If the form responses go to SharePoint list and there are changes/edits/updates to the data, I can see how a calculated column could be helpful. But if it will be static text with no changes, I'd do all of the formatting in power automate so that you are controlling all of the data output at a single place (the power automate flow).

Up to you, the results will be the same. (However lists have a limit to character count within a calculation formula, limit on nested if statements in a formula, as will as a limit of 48 calculated columns per list. So using power automate instead of a calculated column can be more effective in different situations.)

[Nate_willy]

@wskinnermctc,

Thank you so much for these responses. I have definitely considered utilizing Power Automate to consolidate the address fields, but not all of the information in the SharePoint List is static. I work in Student Transportation and what I’m working on here is a registration process that gets us out of the dark ages of having parents come in to the office to fill out paper forms or sending a hand typed email to our org box. With this process, we’ve controlled a lot of the data input through drop downs, radio buttons, and a ton of branching instead of open text entries. So when we receive the info in our list, my staff will be assigning their names to it and marking Info Verified in a choice column I created. When they do that, Power Automate runs a flow to send them a nice little customer friendly email letting them know we’ve received it, checked it, and the transportation specialist assigned to their request is (name). Then they’ll take the address and figure out where the closest bus stop is to their home and assign them to it, also in the list and also with another flow running to notify them of the details.

Sorry to give you the whole process. Just wanted to give you all that so you can see it’s not the address columns that are updated. Those are indeed static. But can I still use power automate to run a flow to combine the columns if other columns change? I’m thinking an if(empty… expression maybe?

oh, maybe I can restrict it by view though in the advanced options of a flow. Thoughts?

Thanks again!

Nate_willy

[wskinnermctc]

Try to get the calculated column working first by using the column name check example above.

It might be better to have the calculated column in this situation because multiple people are using the SP List and might need to correct or edit some address information. They only have to correct the individual box, and the full address will be corrected.

You can at least get the setup running with the calculated column, and then make any changes later if you want.

For example, that format name column from my initial post still exists in the sharepoint list, but I don't use it anymore.

(I personally don't use the View options in flow because it seems to be a problem later if you change the view name, add columns, or other changes. I'd rather deal with all of the data and columns in the flow without a view filter, so I know any view changes won't break my flow.) 

[Nate_willy]

@wskinnermctc,

So I ended up going a totally different direction with this info anyway and no longer require the Concat formula/expression or any others for that matter.

Thank you for all of the assistance though, and I hope you have a good one!

Nate_willy