Text & Data Cleaning

Build Clean UTM & API Query URLs with Cross-Platform Percent-Encoding Formulas

Assemble campaign tracking and webhook URLs from spreadsheet columns while safely percent-encoding %, spaces, ampersands, and plus signs across Windows, Mac, and browser sheets.

Why ENCODEURL fails on Mac/Web and why % must be encoded first

Desktop Excel's ENCODEURL function relies on Windows WinInet APIs and returns #NAME? in Excel for Mac and many browser workbooks. Worse, unencoded campaign names like "Q4 R&D + Ops" break UTM query strings because "&" starts a fake query parameter and "+" decodes as a space.

A portable LET + nested SUBSTITUTE encoder works on every platform—provided you replace literal "%" with "%25" FIRST before replacing spaces ("%20"), "&" ("%26"), "+" ("%2B"), and "#" ("%23"), so newly inserted percent codes are never double-encoded. In shared Arte Sheets campaign trackers, marketing contributors can collaborate in real time and @mention reviewers on flagged URLs before publishing.

Raw Parameter Value	Naive Concatenation Trap	Percent-Encoded Value (% First)
Q4 R&D + Ops	Truncates at "&D" into bogus key	Q4%20R%26D%20%2B%20Ops
50% Off Launch	Encoding % second turns %20 into %2520!	50%25%20Off%20Launch
tier#1 promo	"#1" swallowed as URL fragment anchor	tier%231%20promo

Portable Percent-Encoder for Query Parameter Values

Trims surrounding whitespace, encodes "%" to "%25" first, and then encodes space, "&", "+", and "#" in deterministic order.

=LET(raw, TRIM(C2), s1, SUBSTITUTE(raw, "%", "%25"), s2, SUBSTITUTE(s1, " ", "%20"), s3, SUBSTITUTE(s2, "&", "%26"), s4, SUBSTITUTE(s3, "+", "%2B"), SUBSTITUTE(s4, "#", "%23"))

Full UTM Tracking URL Assembly

Combines Base URL (A2), utm_source (B2), and encoded utm_campaign (D2) using "?" when no query string exists yet or "&" when A2 already contains "?".

=TRIM(A2) & IF(ISNUMBER(FIND("?", A2)), "&", "?") & "utm_source=" & LOWER(TRIM(B2)) & "&utm_campaign=" & D2

Try the example

Use the original values or change them to test your understanding.