Formulas II — Text, Dates & Aggregation

Learning objectives

By the end you can:

1. Extract structured fields from a messy text cell using LEFT, RIGHT, MID, LEN, and the modern TEXTBEFORE / TEXTAFTER / TEXTSPLIT, and choose position-based versus delimiter-based extraction correctly.
2. Clean imported text with TRIM, CLEAN, and SUBSTITUTE (including the non-breaking-space CHAR(160) trap), normalise case with UPPER / LOWER / PROPER, and convert cleaned digits back to real numbers with VALUE.
3. Assemble display strings with TEXT, CONCAT, and TEXTJOIN, and explain why TEXT (formatting to a string) is the inverse skill of VALUE (parsing a string to a number).
4. Explain that Excel dates and times are serial numbers, and use that fact to compute durations, due dates, and ages by direct arithmetic.
5. Do calendar arithmetic with EOMONTH, EDATE, NETWORKDAYS / NETWORKDAYS.INTL, DATEDIF, YEARFRAC, and WEEKDAY, choosing the right function and the right YEARFRAC/weekend basis.
6. Aggregate a column by one or many conditions with SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, and state precisely why the plural *IFS forms (sum-range first, criteria pairs after) are safer and stronger than the legacy singular SUMIF.
7. Choose SUBTOTAL or AGGREGATE over plain SUM when totals must ignore filtered/hidden rows or step over error cells, and pick the correct function-number and options code.
8. Diagnose the three failures that silently break conditional aggregation (numbers stored as text, dates stored as text, and criteria/argument-order mistakes) and repair each.

Prerequisites & connections

Builds on. EX0.01 (the grid, references, the fill handle, F4 to cycle absolute/relative), EX0.02 (IF, SUM, AVERAGE, ROUND, and the idea of a function's argument list), and the whole habit of naming ranges and reading the formula bar. From the analyst path it leans on M1.06 (receivables and inventory, where invoice dates and ageing buckets live) and anticipates M2.05 (benchmarking, which is conditional aggregation applied to peer sets).

Feeds forward. EX1.02 (Lookups Mastery: XLOOKUP, INDEX/MATCH) is the natural sequel: once you can clean and key a table, you can join tables. SUMIFS and a lookup are two sides of the same coin, one totals by a key, the other fetches by a key, and expert modelling reaches for whichever the shape of the answer wants. EX1.03 and EX2 (Tables, PivotTables, Power Query) take everything here and make it refreshable: structured-reference SUMIFS over an Excel Table, then the same logic pushed into a query pipeline. The array-mindset preview at the end is the on-ramp to EX2's dynamic arrays (FILTER, UNIQUE, SORT) and eventually EX5's LAMBDA libraries. Every forensic and modelling module downstream assumes you can do, without thinking, what this module teaches: get a clean number out of a dirty cell and total it by a condition.

Text is data too: TEXT, LEFT/MID, LEN, TRIM

Intuition first. A spreadsheet cell can hold a number, a date (which is a number), or text, a string of characters. Analysts are handed text constantly, and it usually hides the fields we actually want to analyse: a product code encodes a category and a SKU; an invoice string encodes a state and a serial; a "name" column arrives with stray spaces and inconsistent capitalisation that make two identical customers look different to the computer. Text functions exist to parse (pull fields out), clean (remove noise), and reshape (recombine) that data. Master them and you stop retyping data by hand, the single biggest silent time-sink in junior analyst work.

The measuring stick: LEN. LEN(text) returns the number of characters, spaces included. It is the humble diagnostic you reach for before every extraction, because position-based functions need to know where things are. A valid Indian GSTIN is exactly 15 characters, so =LEN("27AABCU9603R1ZM") returns 15 and =IF(LEN(A2)<>15,"CHECK","ok") is a one-cell data-quality flag. LEN counts spaces too, which is precisely why it doubles as a dirt detector: if a "clean" code shows a longer LEN than you expect, there is invisible whitespace in it.

Position-based extraction: LEFT, RIGHT, MID. These slice by character position.

• LEFT(text, n), the first n characters. =LEFT("IN-MUM-4472-A", 2) returns "IN".
• RIGHT(text, n), the last n characters. =RIGHT("INV-00427", 5) returns "00427".
• MID(text, start, n), n characters beginning at position start (1-based). =MID("IN-MUM-4472-A", 4, 3) returns "MUM", start at character 4, take 3.