# Sharepoint Calculated Column formula: where are the extra &'s coming from?

1 Reputation point
2020-10-22T04:02:44.517+00:00

I'm trying to build a calculated column that combines the values of 4 columns to build a URL with the values of the columns as query strings. 3 of the columns can have blank values, so I've written a bit of logic to exclude them if they're empty, but there seems to be a problem because it's writing unnecessary & symbols when there are blank values.

="https://www.example.com/some/path?"&
IF(
ISBLANK([var1]),
IF(
ISBLANK([var2]),
IF(
ISBLANK([var3]),
[Payment Term QS],
CONCATENATE([var3],"&",[constant])
),
IF(
ISBLANK([var3]),
CONCATENATE([var2],"&",[constant]),
CONCATENATE([var3],"&",[var2],"&",[constant])
)
),
IF(
ISBLANK([var2]),
IF(
ISBLANK([var3]),
CONCATENATE([var1],"&",[constant]),
CONCATENATE([var3],"&",[var1],"&",[constant])
),
IF(
ISBLANK([var3]),
CONCATENATE([var2],"&",[var1],"&",[constant]),
CONCATENATE([var3],"&",[var2],"&",[var1],"&",[constant])
)
)
)

For example, if var2 is blank, it's writing:
https://www.example.com/some/path?var3&&var1&constant

The double && is wrong.

If var3 is blank, then it generates:
https://www.example.com/some/path?&var2&var1&constant

The first & after the ? is wrong.

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,559 questions

1. 6,326 Reputation points
2020-10-22T07:03:40.017+00:00

I have tested with the formula you provide, and I do not seem to reproduce this issue.
Try adding a parentheses mark around the IF conditions to generate string:
https://support.microsoft.com/en-us/office/examples-of-common-formulas-in-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3

``````="https://www.example.com/some/path?"&(
IF(ISBLANK([var1]),
IF(ISBLANK([var2]),
IF(ISBLANK([var3]),[Payment Term QS],
CONCATENATE([var3],"&",[constant])
),
IF(ISBLANK([var3]),CONCATENATE([var2],"&",[constant]),
CONCATENATE([var3],"&",[var2],"&",[constant])
)
),
IF(ISBLANK([var2]),
IF(ISBLANK([var3]),CONCATENATE([var1],"&",[constant]),
CONCATENATE([var3],"&",[var1],"&",[constant])
),
IF(ISBLANK([var3]),CONCATENATE([var2],"&",[var1],"&",[constant]),
CONCATENATE([var3],"&",[var2],"&",[var1],"&",[constant])
)
)
)
)
``````

Test outcome: