Does LEFT work natively with ARRAYFORMULA in Google Sheets?

Yes. As of June 2026, LEFT is fully array-compatible. `=ARRAYFORMULA(LEFT('Sheet1'!A2:A, 3))` processes the entire column range and returns results for every populated row. No special syntax beyond wrapping with ARRAYFORMULA is required. This is consistent with Google's documented behavior for string functions.

Why does ARRAYFORMULA(LEFT(...)) return empty strings instead of blank cells?

LEFT returns `""` for empty input cells rather than a true blank. If your downstream SUMIFS or VLOOKUP checks `<>""` to skip blanks, those empty strings will match and may cause errors. Fix with: `=ARRAYFORMULA(IF(A2:A="","",LEFT(A2:A,2)))`. This returns a genuine empty cell for blank rows and the extracted text for populated ones.

Can I use the result of ARRAYFORMULA(LEFT(...)) inside a SUMIFS range argument?

Not directly as an inline formula reference. SUMIFS expects a defined range, not a formula result, in its range arguments. Either materialize the ARRAYFORMULA output by writing it to a helper column (and referencing that column in SUMIFS), or restructure using SUMPRODUCT: `=SUMPRODUCT((LEFT('Transactions'!C2:C1000,2)="40")*'Transactions'!E2:E1000)`. SUMPRODUCT handles arrays natively without the range-reference constraint.

What happens when LEFT extracts from a numeric cell?

LEFT coerces numbers to text automatically. `LEFT(41002,2)` returns the text string `"41"`, not the number 41. If you need the extracted value as a number for arithmetic downstream (e.g., as a lookup key in a numeric range), wrap with VALUE: `=ARRAYFORMULA(VALUE(LEFT(range,2)))`. Skip VALUE if the downstream function accepts text keys, like VLOOKUP matching against a text-formatted account code column.

Does making the second argument (num_chars) an array work with ARRAYFORMULA?

Yes. You can pass a column range as the num_chars argument: `=ARRAYFORMULA(LEFT(A2:A, B2:B))`, where column B holds the extraction length per row. This is useful in multi-entity consolidations where account code prefix lengths differ by entity. Both range arguments must be the same length or one must be a scalar; mismatched ranges produce #VALUE errors.

Google Sheets LEFT + ARRAYFORMULA Guide (2026)

What the Official Documentation Actually Says

Google's ARRAYFORMULA documentation describes it as enabling "display of values returned from an array formula into multiple rows and/or columns." It lists string functions as array-compatible but doesn't explicitly enumerate every one.

The LEFT documentation covers single-cell usage: LEFT(string, [number_of_characters]), returning characters from the start of a string. What it doesn't show you is the array-expanded form, which is where the real productivity is.

In practice (as of June 2026), LEFT is fully array-compatible. You can pass an entire range as the first argument, a constant as the second, and ARRAYFORMULA handles the expansion. Google's behavior here is consistent and reliable across the current Sheets version.

The Core Pattern

=ARRAYFORMULA(LEFT(range, n))

Applied to a real GL model pulling account codes from a transaction detail tab:

=ARRAYFORMULA(LEFT('GL Detail'!C2:C, 2))

This drops a 2-character prefix into every populated row - no fill-down, no range maintenance when rows are added. For a transactions tab with 4,000+ rows, this single formula replaces 4,000 individual =LEFT(C2,2) entries and recalculates faster.

The Empty-Cell Problem (and Fix)

The one gotcha that bites people: ARRAYFORMULA(LEFT(range, n)) returns an empty string for blank cells, which is usually fine. But if you're feeding the output into a SUMIFS or VLOOKUP, empty strings behave differently from TRUE blanks and can produce unexpected #N/A errors downstream.

Fix it with an IF wrapper:

=ARRAYFORMULA(IF('GL Detail'!C2:C="","",LEFT('GL Detail'!C2:C,2)))

This keeps blank rows genuinely blank rather than returning "", which matters when the downstream formula checks <>"".

Real Finance Use Cases

GL account classification. Your chart of accounts uses a 6-digit code structure where the first 2 digits define the major category (10=Assets, 20=Liabilities, 40=Revenue, 50=COGS, 60=OpEx). Rather than maintaining a separate mapping column:

=ARRAYFORMULA(IF('Transactions'!D2:D="","",
  VLOOKUP(LEFT('Transactions'!D2:D,2),
  'COA'!$A$2:$B$50,2,FALSE)))

This single formula in your P&L tab maps every transaction to its income statement category by stripping the first 2 characters and looking them up in your chart of accounts tab.

SKU-level contribution margin by category. If SKUs follow a prefix convention (where the first 3 characters encode the product line - e.g., "HW-" for Hardware, "SW-" for Software, "SVC-" for Services), you can slice contribution margin without a pivot:

=SUMIFS('Revenue Detail'!E:E,
  ARRAYFORMULA(LEFT('Revenue Detail'!B:B,3)),
  Assumptions!$B$4)

Note: SUMIFS with an ARRAYFORMULA-derived column works when the array is an actual named range or spilled into the sheet. If you're referencing the live formula output inside SUMIFS, wrap it differently - often easier to materialize the LEFT extraction into a helper column on the source tab and reference that.

Cost center extraction from account strings. Many ERP exports concatenate account and cost center into a single string like "6100-CC204". To extract just the cost center for a departmental expense roll-up:

=ARRAYFORMULA(IF('ERP Export'!A2:A="","",
  MID('ERP Export'!A2:A,
  FIND("-",'ERP Export'!A2:A)+1,
  LEN('ERP Export'!A2:A))))

That's MID rather than LEFT, but the pattern is identical - one formula processing the entire column.

Variable Extraction Length

Most examples treat the second argument (number of characters) as a constant. It doesn't have to be. If your account code length varies by entity (common in multi-entity consolidations), you can pull the extraction length from an assumptions cell:

=ARRAYFORMULA(LEFT('GL Detail'!C2:C, Assumptions!$B$12))

Where Assumptions!$B$12 holds the prefix length for the current entity. Swap that cell and the entire classification column updates. This is cleaner than hardcoding 2 throughout the workbook when entity-specific configuration lives in one place.

When LEFT + ARRAYFORMULA Isn't the Right Call

Three situations where you'd reach for something else:

Variable-position delimiters. If your account codes don't have a fixed prefix length, REGEXEXTRACT is more reliable than LEFT: =ARRAYFORMULA(REGEXEXTRACT('GL Detail'!C2:C,"^([A-Z]+)")).

The source column is numeric. LEFT coerces numbers to text automatically (so LEFT(41002,2) returns "41"), but if your downstream formula expects a number, you'll need VALUE() to convert back. Add it: =ARRAYFORMULA(VALUE(LEFT(range,2))).

You need the result in another formula's range argument. Some functions won't accept an ARRAYFORMULA-derived result as a range reference. In that case, materialize the LEFT extraction into a static helper column with Paste Special > Values, or use a query-based approach.

Performance Note

A single =ARRAYFORMULA(LEFT(range, 2)) processing 5,000 rows recalculates in under 100ms in typical Sheets workbooks. Five thousand individual LEFT formulas can push recalculation time above 3 seconds on complex multi-tab models, especially with volatile functions elsewhere triggering full recalcs. For board pack models where you're refreshing assumptions frequently, this difference adds up.

If your formula pipeline involves AI-assisted formula generation across tabs, ModelMonkey can write and apply these ARRAYFORMULA patterns directly into your sheet from a natural language prompt - useful when you're building the classification layer of a new model and don't want to hand-write every cross-tab reference.