Whitespace around a reference

What it does

SUM( reference )

Ordinary formula whitespace around a reference can be padding. In =SUM( B2 ), the padded reference still names B2; the surrounding spaces do not change which cell SUM reads.

Padding around one reference must be distinguished from a space joining two reference expressions. The latter can calculate an intersection.

Try it

Calculate =SUM( B2 ) with the supplied B2 value. Compare =SUM(B2) and =SUM(B2:C3 B3:C4): the last formula uses a space as an intersection operator and reads only the cells common to both ranges.

Quirks

Whitespace cannot be discarded blindly before parsing. Excel uses a space between reference expressions for intersection. Confusing that use with padding changes the referenced cells and can introduce #NULL! when the intersection is empty.

An at sign has its own meaning: @ requests implicit intersection. It is not a general spelling for trimming leading or trailing spaces. Whitespace inside quoted text is data and must not be removed by formula normalization.

How to implement

Retain whitespace as syntax information until the parser can determine whether it separates two reference expressions. When it is padding, keep the operand's reference identity and extent unchanged. When it is intersection, construct that reference operation instead.

Preserve the user's formula spelling separately from the evaluated reference. Share the lookahead and token classification across the parser and formatter so that saving or displaying a formula does not reinterpret its spaces. Test padding next to parentheses, commas, range and spill operators, plus quoted text containing spaces. Tabs, line breaks and nonbreaking spaces need separate lexical rules; this page does not treat every Unicode space as equivalent.

Rust code

This entry has no Rust implementation yet.

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

Version differences

No build-by-build comparison of whitespace normalization is recorded here. Dynamic-array @ notation is a separate feature with its own compatibility rules.

Alternatives

Use the range operator for a rectangular reference, the union operator to combine references, and intersection to retain their common cells. TRIM changes text contents rather than parsing formula whitespace.

Named variants

No named variants are recorded yet.