Calculated Column Syntax Error Not Supported

[Nate_willy]

Hello,

I've built an MS Form that feeds to a SharePoint List via Power Automate. In the Form, I have a good bit of branching to make it more customer friendly. However, in the SharePoint List, this has created a lot of blank columns. For example, I deal with military bases and customers need to select a base, a housing area on the base, and then a building & unit number within the housing area. I added branching within the Form to filter basically filter out the housing and show only the ones on the base that they choose. Since there are so many blanks with this, it makes it a bit difficult for my staff to quickly locate and copy/paste a single address line. Instead, they have to hunt the address fields that are filled down amongst the blanks. I read that there is a formula using CONCATENATE, TRIM, IF, and ISBLANK functions and I followed the same logic that someone else posted in the Power Automate forum as a solution. However, I keep getting back "Syntax Error Not Supported" and I can't figure out why it isn't working. Below is the calculation I'm attempting to put into a calculated column in my SharePoint List. Any assistance would be greatly appreciated!

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

OR,

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

I have tried both formulas with and without square brackets around the other column titles as well.

Respectfully,

Nate_willy

[#2]

@Nate_willy Try using this formula: 

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

Notes:

1. Make sure you are using correct display names of your columns in the formula. Column names are case sensitive. For safer side, enclose all column names with square brackets
2. Sometimes comma( , ) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon( ; ) instead of comma ( , ).
3. You can add up to 30 parameters inside concatenate function.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs