ENCODEURL

What it does

ENCODEURL(text)

ENCODEURL is the odd one out among Excel's three Web functions. WEBSERVICE makes a network request and FILTERXML parses what comes back; ENCODEURL does neither. It is a string transformation, computable offline, deterministic, with no host and no service behind it.

ENCODEURL(text) returns a URL-encoded copy of text: Microsoft's description is that it replaces certain non-alphanumeric characters with "the percentage symbol (%) and a hexadecimal number".

The documented example fixes the strength of the encoding, which is the fact worth taking away from this page. Given

http://contoso.sharepoint.com/Finance/Profit and Loss Statement.xlsx

Microsoft documents the result as

http%3A%2F%2Fcontoso.sharepoint.com%2FFinance%2FProfit%20and%20Loss%20Statement.xlsx

Two things follow directly from that one example:

1. It is a component encoder, not a URL sanitizer. The colon and the slashes of the scheme and path are themselves encoded (%3A, %2F). ENCODEURL does not preserve URL structure — it destroys it. Feeding a whole URL to ENCODEURL and using the result as a URL will not work. The function is for encoding a value you are about to place inside a URL: a query parameter, a path segment, a search term.
2. Space becomes %20, not +. The two conventions differ (+ belongs to application/x-www-form-urlencoded), and mixing them is a classic source of silently wrong query strings. The documented example settles which one Excel uses.

That is the whole of what the documentation pins. It does not enumerate the character set that survives unencoded, and it does not state how non-ASCII text is handled — both are listed under what has not been checked.

Arguments

• text (required) — Microsoft: "A string to be URL encoded".

The function declares a TextToText kernel, so a non-text argument reaches it through ordinary scalar coercion — numbers and logicals convert to text under the rules in coercion and lifting, and an error argument propagates rather than being encoded. Nothing here is specific to ENCODEURL; the shared chapter governs it.

Try it

Quirks

Result and edge cases

Text. The published result is subject to the ordinary 32,767-code-unit cell limit described in the value universe, and percent-encoding expands its input — up to threefold per byte — so a long input can cross the cap after encoding even though it was comfortably under it before. That interaction is not something the documentation mentions and is the most likely practical failure mode.

Two edges the documentation leaves open, and which this page will not guess at:

• Empty input. Whether an empty string or an empty referenced cell yields an empty string.
• Non-BMP characters. Emoji and other astral characters occupy two UTF-16 code units each (see the value-universe chapter's text model). Percent-encoding is defined over bytes, so the function must be performing a UTF-8 (or other) transcoding step that the documentation never names.

Errors

Microsoft's ENCODEURL page documents no error conditions. That is not the same as the function being infallible — the cell text cap alone gives it a reachable failure mode — but the Handbook has nothing to cite here.

The platform restriction is stated firmly and belongs in this section as much as in any: Microsoft documents that ENCODEURL is not available in Excel for the web or Excel for Mac, and adds that it "may appear in Excel for Mac's function gallery, but it relies on Windows operating system features, so it will not return results on Mac". A function that appears in the gallery and then does not work is a distinct, poorly-signposted state, and it is particularly striking for ENCODEURL, since percent-encoding has no obvious dependency on Windows at all. The dependency is inherited from the family's implementation, not from the mathematics.

How to implement

Notes for implementers

• The unreserved set is the whole specification, and the documentation does not give it. RFC 3986 leaves A–Z a–z 0–9 - . _ ~ unencoded; older escape-style encoders also pass !, *, ', (, ). Which of these Excel passes through is unknown to the Handbook and cannot be inferred from the single documented example, whose input contains none of them.
• Percent-encoding operates on bytes, so a text-to-bytes step is required and its encoding choice is observable. UTF-8 is the modern assumption; a Windows-API-backed implementation could plausibly do something else for non-ASCII input.
• Hexadecimal case (%2F versus %2f) is a free choice for an encoder and a fixed fact for a compatibility implementation. The documented example uses uppercase.
• The output length can exceed the input length by up to a factor of nine for astral characters (four UTF-8 bytes at three characters each, from two UTF-16 input units). Any implementation that pre-sizes a buffer from input length is wrong.

Rust code

19 encodeurl_fn "ENCODEURL" (1, 1) [V "text"] |s, a, sv| { encodeurl(s, a) }

/// ENCODEURL: the text's UTF-8 bytes with every byte but the ASCII letters, digits, `-`, `.` and
/// `_` written as `%` and two upper-case hex digits; a number or logical is first read as text, as
/// `&` reads it. A result longer than a cell's text is #VALUE!. Excel 16.0.20430 (dc-uar, the
/// catalogue oracle's sheet dcuar_text): `~` is encoded, `ENCODEURL("a b")` is `a%20b`,
/// `ENCODEURL(1E+20)` is `1E%2B20`, and `LEN(ENCODEURL(REPT("é",10000)))` is #VALUE!. Text that is
/// not valid UTF-16 (a lone surrogate) is refused as not modelled.
fn encodeurl(site: &Site<'_>, a: &[CalcValue]) -> Step<CalcValue> {
    let text = super::kit::text(site, a, 0)?;
    let mut out: Vec<u16> = Vec::with_capacity(text.len());
    for byte in text.bytes() {
        if byte.is_ascii_alphanumeric() || matches!(byte, b'-' | b'.' | b'_') {
            out.push(u16::from(byte));
        } else {
            const HEX: &[u8; 16] = b"0123456789ABCDEF";
            out.push(u16::from(b'%'));
            out.push(u16::from(HEX[usize::from(byte >> 4)]));
            out.push(u16::from(HEX[usize::from(byte & 15)]));
        }
        if out.len() > crate::value::EXCEL_TEXT_MAX_UTF16_CODE_UNITS {
            return fail(WorksheetErrorCode::Value);
        }
    }
    Ok(CalcValue::text(ExcelText::from_utf16_code_units(out)))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• WEBSERVICE — the intended consumer. Microsoft's own examples compose them: =WEBSERVICE("…?symbol="&ENCODEURL(C2)), and the WEBSERVICE page recommends encoding the URL before use. The composition is the point of the function.
• FILTERXML — the third member of the trio, applied to what WEBSERVICE returns. Microsoft's documented three-function idiom is =FILTERXML(WEBSERVICE("…"&ENCODEURL(C2)), "//path").
• SUBSTITUTE — what workbooks used before ENCODEURL existed, chained once per character class. Anyone maintaining such a formula is looking at a hand-rolled, usually incomplete, version of this function.
• HYPERLINK — frequently confused with it. HYPERLINK builds a clickable link; ENCODEURL produces text. Encoding a whole URL and passing it to HYPERLINK produces a link that does not resolve, for the component-encoder reason given above.

Named variants

No named variants are recorded yet.