Here follows the changes in the Excel formula from version 2 to version 3 of the Mason Campaign URL Builder.
The original formula from version 2:
=IF(AND(LEN(A20)>0,LEN(C20)>0,LEN(B20)>0,LEN(D20)>0),TRIM(A20) &”?utm_source=” & SUBSTITUTE(TRIM(C20),” “,”+”) & “&utm_medium=” & SUBSTITUTE(TRIM(B20),” “,”+”) & “&utm_campaign=” & SUBSTITUTE(TRIM(D20),” “,”+”) & IF(LEN(E20)>0,”&utm_term=” & SUBSTITUTE(TRIM(E20),” “,”+”),””) & IF(LEN(F20)>0,”&utm_content=” & SUBSTITUTE(TRIM(F20),” “,”+”),””),””)
This switches order of source and medium, and replaces plus signs with dashes:
=IF(AND(LEN(A20)>0,LEN(C20)>0,LEN(B20)>0,LEN(D20)>0),TRIM(A20) &”?utm_medium=” & SUBSTITUTE(TRIM(B20),” “,”-“) & “&utm_source=” & SUBSTITUTE(TRIM(C20),” “,”-“) & “&utm_campaign=” & SUBSTITUTE(TRIM(D20),” “,”-“) & IF(LEN(E20)>0,”&utm_term=” & SUBSTITUTE(TRIM(E20),” “,”-“),””) & IF(LEN(F20)>0,”&utm_content=” & SUBSTITUTE(TRIM(F20),” “,”-“),””),””)
This adds support for business unit field:
=IF(AND(LEN(A20)>0,LEN(C20)>0,LEN(B20)>0,LEN(D20)>0),TRIM(A20) &”?utm_medium=” & SUBSTITUTE(TRIM(B20),” “,”-“) & “&utm_source=” & IF(Len(B$8)>0,B$8&”-“,””) & SUBSTITUTE(TRIM(C20),” “,”-“) & “&utm_campaign=” & SUBSTITUTE(TRIM(D20),” “,”-“) & IF(LEN(E20)>0,”&utm_term=” & SUBSTITUTE(TRIM(E20),” “,”-“),””) & IF(LEN(F20)>0,”&utm_content=” & SUBSTITUTE(TRIM(F20),” “,”-“),””),””)
This adds support for business unit subgroup field:
=IF(AND(LEN(A20)>0,LEN(C20)>0,LEN(B20)>0,LEN(D20)>0),TRIM(A20) &”?utm_medium=” & SUBSTITUTE(TRIM(B20),” “,”-“) & “&utm_source=” & IF(Len(B$8)>0,B$8 & “-“,””) & IF(Len(B$9)>0,B$9 & “-“,””) & SUBSTITUTE(TRIM(C20),” “,”-“) & “&utm_campaign=” & SUBSTITUTE(TRIM(D20),” “,”-“) & IF(LEN(E20)>0,”&utm_term=” & SUBSTITUTE(TRIM(E20),” “,”-“),””) & IF(LEN(F20)>0,”&utm_content=” & SUBSTITUTE(TRIM(F20),” “,”-“),””),””)