--- title: 'Text Functions' source: 'https://academia.sh/en/courses/analysis-with-spreadsheets/text-functions' course: 'Analysis with Spreadsheets' language: en updated: '2026-10-07T04:31:09+00:00' license: 'CC BY-SA 4.0' objectives: - 'Separates the trimming, case conversion, and whitespace removal decisions and counts how many records each one matches' - 'Shows that an invisible space is not removed by ordinary trimming and that case conversion depends on the language' - 'Compares whether the cleaning decision sits in a helper column or in a formula by whether it can be read back afterward' --- # Text Functions 48 field visit records are matched to a 60-subscriber list on the subscriber code, and in raw form only 8 of them match. Trimming raises that to 24, trimming plus uppercase conversion to 32, and removing every kind of whitespace to 48; the consumption reported for the visits swings between 232.1 m³ and 1204.8 m³ across these decisions. A fifth decision that rebuilds the code yields the same 48 while silently attaching a record that arrives with a different prefix to the wrong subscriber. A locale-insensitive case conversion misses 15 of the technician names, and of three versions that produce the same result, only one leaves the 40 correction decisions readable back cell by cell. Until now every comparison was numeric. The other half of the sheet is text, and in text equality is not as obvious as it is with numbers: `AB-1041` and `AB-1041 ` look the same on screen, do not look the same length, and do not come out equal when compared. This lesson matches the field visit records to the subscriber list on the subscriber code and counts how many records **fail to join**. The measure is read in two places. The first is the number of matched records and the consumption value that goes into the report along with it. The second is where the cleaning decision sits: in a helper column, inside the matching formula, or nowhere at all — because a cell corrected by hand shows the right result and destroys the decision. - **BO13.** The subscriber codes in the visits table arrive in six forms: clean, trailing space, leading space, lowercase, trailing **non-breaking space**, inner space. The corruptions are fictional and are distributed in order with their own seed. The type of corruption is **not written** on the sheet; the `kind` function below can classify it only by inspection. - **BO14.** The `trim` function removes only the **normal space**; the non-breaking space and the inner space remain untouched. `strip` removes every kind of whitespace. The difference between the two is the most expensive decision of this lesson. - **BO15.** `upper` is a locale-insensitive case conversion; `upperTr` applies the Turkish rule for the letter i. The crew names are fictional and half of them were entered in lowercase. ## Identical to the Eye, Different in Comparison The block below builds two tables: on the left the 60-subscriber list, on the right the 48 visit records. In the output, spaces are turned into visible characters — a normal space becomes an underscore, a non-breaking space becomes a tilde. ```js // text.mjs — spreadsheet MODEL: the model from 01, extended with text functions. // The subscriber codes in the field visits table arrive corrupted; the corruptions are fictional. export const SEED = 20260602, ROWS = 60, VISITS = 48; const rng = (t) => { let s = t >>> 0; return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; }; export const NBSP = "\u00a0"; // invisible space: identical to a normal space in a cell const REF = /(\$?)([A-Z])(\$?)(\d+)/g; export const shift = (f, dC, dR) => f.replace(REF, (_, a, s, b, r) => (a ? "$" + s : String.fromCharCode(s.charCodeAt(0) + dC)) + (b ? "$" + r : String(+r + dR))); // Text functions. upper is locale-insensitive; upperTr applies the Turkish i/I rule. const FUNCS = { trim: (s) => String(s).replace(/^ +| +$/g, ""), // only the normal space is trimmed upper: (s) => String(s).toUpperCase(), upperTr: (s) => String(s).replace(/i/g, "İ").replace(/ı/g, "I").toUpperCase(), strip: (s) => String(s).replace(/\s/g, ""), // \s covers the invisible space too concat: (...p) => p.map(String).join(""), right: (s, n) => String(s).slice(-n), // last n characters: the numeric part of the code length: (s) => String(s).length, }; export function value(H, name) { const v = H[name]; if (v === undefined) return 0; if (typeof v !== "string" || v[0] !== "=") return v; const expr = v.slice(1).replace(REF, (m) => JSON.stringify(value(H, m.replace(/\$/g, "")))); return Function(...Object.keys(FUNCS), `return (${expr});`)(...Object.values(FUNCS)); } // Six types of corruption; they cannot be told apart by eye on the sheet. export const DEFECTS = [["clean", (k) => k], ["trailing space", (k) => k + " "], ["leading space", (k) => " " + k], ["lowercase", (k) => k.toLowerCase()], ["non-breaking space", (k) => k + NBSP], ["inner space", (k) => k.replace("-", "- ")]]; export const CREW = ["İsmet", "İlknur", "Irmak", "Ilgaz", "Sedef"]; export function sheet() { const r = rng(SEED), H = {}; H.A1 = "subscriber"; H.C1 = "consumption"; H.P1 = "visit code"; H.Q1 = "technician"; for (let i = 0; i < ROWS; i++) { const s = i + 2; H["A" + s] = "AB-" + String(1041 + i * 7).padStart(4, "0"); r(); // the region column from 01: rng runs in the same order H["C" + s] = Math.round((2 + r() * r() * 78) * 10) / 10; } // Visits table: VISITS distinct subscribers, each type of corruption applied in turn. const z = rng(SEED + 2); const order = Array.from({ length: ROWS }, (_, i) => i); for (let i = ROWS - 1; i > 0; i--) { const j = Math.floor(z() * (i + 1)); [order[i], order[j]] = [order[j], order[i]]; } for (let i = 0; i < VISITS; i++) { const s = i + 2, k = H["A" + (2 + order[i])]; H["P" + s] = DEFECTS[i % DEFECTS.length][1](k); // Half were entered in lowercase; the lowercase conversion follows the Turkish rule. const t = CREW[Math.floor(z() * CREW.length)]; H["Q" + s] = z() < 0.5 ? t.replace(/İ/g, "i").replace(/I/g, "ı").toLowerCase() : t; } return H; } // The type of corruption is not written on the sheet; it can be classified only by inspection. export const kind = (k) => k !== k.replace(/^ +| +$/g, "") ? "trimmable space" : k.includes(NBSP) ? "non-breaking space" : k.includes("- ") ? "inner space" : k !== k.toUpperCase() ? "lowercase" : "clean"; if (import.meta.url === `file://${process.argv[1]}`) { const H = sheet(); const show = (s) => String(s).replace(new RegExp(NBSP, "g"), "~").replace(/ /g, "_"); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const y = g([8, 14, 8, 20]); console.log(y(["cell", "visit code", "length", "defect"])); for (const s of [2, 3, 4, 5, 6, 7]) console.log(y(["P" + s, show(H["P" + s]), value({ ...H, X1: "=length(P" + s + ")" }, "X1"), kind(H["P" + s])])); const P = Array.from({ length: VISITS }, (_, i) => H["P" + (i + 2)]); const K = new Set(Array.from({ length: ROWS }, (_, i) => H["A" + (i + 2)])); const tally = {}; for (const k of P) tally[kind(k)] = (tally[kind(k)] ?? 0) + 1; console.log(`\n${VISITS} visit records — ` + Object.entries(tally).map(([a, n]) => `${a} ${n}`).join(", ")); console.log(`Records whose code is found exactly as in the subscriber table: ${P.filter((k) => K.has(k)).length}`); } ``` ``` cell visit code length defect P2 AB-1286 7 clean P3 AB-1083_ 8 trimmable space P4 _AB-1167 8 trimmable space P5 ab-1090 7 lowercase P6 AB-1097~ 8 non-breaking space P7 AB-_1048 8 inner space 48 visit records — clean 8, trimmable space 16, lowercase 8, non-breaking space 8, inner space 8 Records whose code is found exactly as in the subscriber table: 8 ``` The length column is the lesson's only visible evidence. `P3` and `P6` are eight characters long and in both the eighth character is a space; but one is trimmed and the other is not. On the sheet both look like a seven-character code. **What a text cell shows is not what it contains.** In raw form only 8 of the 48 records are found in the subscriber list. The remaining 40 records do not belong to vanished subscribers; all of them belong to subscribers who are in the list, and none of them match. ## Four Cleaning Decisions Cleaning is not a single operation but a sequence of decisions, and each decision brings back a group of records. The block below applies the four in a helper `R` column and prints, at each stage, the number of matched records together with the consumption that would go into the report. ```js // matching.mjs — four cleaning decisions, four match counts, and four reported consumption values. import { sheet, value, shift, ROWS, VISITS } from "./text.mjs"; const DECISIONS = [ ["raw", "=P2"], ["trim", "=trim(P2)"], ["trim + uppercase", "=upper(trim(P2))"], ["every space + uppercase", "=upper(strip(P2))"], ["rebuild", '=concat("AB-",right(strip(P2),4))'], ]; const H = sheet(); const CONSUMPTION = new Map(Array.from({ length: ROWS }, (_, i) => [H["A" + (i + 2)], H["C" + (i + 2)]])); const keys = (f) => { const K = { ...H }; for (let i = 0; i < VISITS; i++) K["R" + (i + 2)] = shift(f, 0, i); return Array.from({ length: VISITS }, (_, i) => value(K, "R" + (i + 2))); }; const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const y = g([26, 10, 12, 18, 12]); console.log(y(["R column formula", "matched", "unmatched", "consumption (m3)", "recovered"])); let previous = 0; for (const [name, f] of DECISIONS) { const R = keys(f), m = R.filter((k) => CONSUMPTION.has(k)); const t = m.reduce((a, k) => a + CONSUMPTION.get(k), 0); console.log(y([name, m.length, VISITS - m.length, t.toFixed(1), m.length - previous])); previous = m.length; } const total = Array.from({ length: ROWS }, (_, i) => H["C" + (i + 2)]).reduce((a, b) => a + b, 0); console.log(`\nTotal consumption of the 60 subscribers is ${total.toFixed(1)} m3; visited consumption is ` + `${keys(DECISIONS[3][1]).reduce((a, k) => a + (CONSUMPTION.get(k) ?? 0), 0).toFixed(1)} m3.`); // The rebuild does not read the code, it rewrites it: a record with another prefix matches too. const foreign = { ...H, P2: "XY-1041" }; for (const [name, f] of DECISIONS.slice(3)) { const K = { ...foreign, R2: f }; console.log(`${name}: XY-1041 -> ${value(K, "R2")} (${CONSUMPTION.has(value(K, "R2")) ? "matched" : "unmatched"})`); } ``` ``` R column formula matched unmatched consumption (m3) recovered raw 8 40 232.1 8 trim 24 24 527.6 16 trim + uppercase 32 16 673.7 8 every space + uppercase 48 0 1204.8 16 rebuild 48 0 1204.8 0 Total consumption of the 60 subscribers is 1419.9 m3; visited consumption is 1204.8 m3. every space + uppercase: XY-1041 -> XY-1041 (unmatched) rebuild: XY-1041 -> AB-1041 (matched) ``` The number that goes into the report is in the fourth column of this table, and it has four values: 232.1, 527.6, 673.7, and 1204.8 m³. There is more than a fivefold difference between the smallest and the largest, and all four came out of the same data, the same question, on the same day. The sentence "the total consumption of the visited subscribers" does not by itself say which of these four numbers is meant. Trimming alone brought back 16 records, and stopping there is easy: 24 matches look plausible, and the remaining 24 records can be explained away as "subscribers who are not in the list." Yet all 24 of those records are in the list; 8 carry a non-breaking space, 8 carry an inner space, and both survive trimming. **Whether a cleaning is sufficient cannot be told by looking at the cleaned count.** The fifth row gives the same count by a third route, and it is the most dangerous. The rebuild does not read the code, it **rewrites** it: it takes the last four characters and adds a fixed prefix in front. The last two rows show the price of that. An `XY-1041` record that arrives with another prefix does not match under the fourth decision; under the fifth it silently becomes `AB-1041` and is attached to an unrelated subscriber. The match count stays the same, and the error is invisible. ## Case Conversion and Where the Decision Sits Case conversion is not a single operation either. The block below folds the technician names with two conversions and compares them with the crew list, then scans three versions to see where the cleaning decision sits on the sheet. ```js // decision.mjs — the language dependence of case conversion, and where the cleaning decision sits on the sheet. import { sheet, value, shift, CREW, VISITS, NBSP } from "./text.mjs"; const H = sheet(); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const column = (f, n) => { const K = { ...H }; for (let i = 0; i < n; i++) K["S" + (i + 2)] = shift(f, 0, i); return Array.from({ length: n }, (_, i) => value(K, "S" + (i + 2))); }; // The crew list is folded with the same function; the match is the equality of two folded texts. const y = g([34, 10, 12, 22]); console.log(y(["S column formula", "matched", "unmatched", "failing names"])); for (const [name, f] of [["locale-insensitive", "=upper(Q2)"], ["Turkish rule", "=upperTr(Q2)"]]) { const S = column(f, VISITS); const list = CREW.map((t) => value({ X1: "=" + f.slice(1).replace("Q2", JSON.stringify(t)) }, "X1")); const m = S.filter((v) => list.includes(v)).length; const miss = [...new Set(S.filter((v) => !list.includes(v)))].join(" "); console.log(y([name + " (" + f + ")", m, VISITS - m, miss || "-"])); } // Three versions that produce the same result: the decision in a helper column, in a formula, or nowhere. const VERSIONS = [ ["helper column", 'R column =upper(strip(P2))', VISITS, true], ["embedded", "inside the matching formula", 0, true], ["manual fix", "written over the P column", 0, false], ]; const P = Array.from({ length: VISITS }, (_, i) => H["P" + (i + 2)]); const broken = P.filter((k) => k !== k.replace(/^ +| +$/g, "") || k.includes(NBSP) || k.includes("- ") || k !== k.toUpperCase()).length; const z = g([16, 30, 8, 10, 12]); console.log("\n" + z(["version", "where the decision is", "cells", "raw data", "read-back"])); for (const [name, place, cells, raw] of VERSIONS) console.log(z([name, place, cells, raw ? "kept" : "erased", cells ? broken : 0])); console.log(`\n${VISITS} visit records, ${broken} of them broken. In the helper column these ${broken} ` + `decisions are read one by one; in the manual fix none is read and the raw code does not remain either.`); ``` ``` S column formula matched unmatched failing names locale-insensitive (=upper(Q2)) 33 15 ISMET ILKNUR Turkish rule (=upperTr(Q2)) 48 0 - version where the decision is cells raw data read-back helper column R column =upper(strip(P2)) 48 kept 40 embedded inside the matching formula 0 kept 0 manual fix written over the P column 0 erased 0 48 visit records, 40 of them broken. In the helper column these 40 decisions are read one by one; in the manual fix none is read and the raw code does not remain either. ``` The first table states that case conversion is not a text operation but a **language** operation. The locale-insensitive conversion matched 33 of the 48 names; the 15 that failed belong to two names. The first letter of the names "ismet" and "ilknur," entered in lowercase, is converted to an undotted I under the locale-insensitive conversion and does not match the dotted form in the list. Under the conversion that applies the Turkish rule, all 48 names match. The function is called "convert to uppercase" in both; they do not do the same job. The second table gives the lesson's second number. All three versions have the same match result: 48 records match. The difference is in the last three columns. In the helper column version the decision sits in 48 cells; the cleaned code is visible, the raw code stays in place, and the question "what was done" for each of the 40 records that arrived broken is answered by looking at the cell. In the formula-embedded version the result is the same, but the cleaned code is visible nowhere on the sheet; which visit was attached to which subscriber can be guessed only by reading the formula text. The third row is the most expensive one and the most common one. When the codes are corrected by hand and written over the `P` column, the sheet looks flawless: every code is clean, every match holds, and there is no helper column. The number of decisions that can be read back is **zero**, and the raw data no longer exists. Someone who looks a second time cannot see that 40 records were corrected, cannot know which rule was applied, and cannot check whether the correction was right. **An operation that fixes the result, if it destroys the decision, destroys the data too.** ## Summary - What a text cell shows is not what it contains: two codes that look the same can have different lengths and do not come out equal in a comparison. - Of the 48 visit records, 8 matched in raw form; trimming raised that to 24, case conversion to 32, and removing every kind of whitespace to 48. The reported consumption swung between 232.1 and 1204.8 m³. - Ordinary trimming removes only the normal space; the 16 records carrying a non-breaking space or an inner space survive trimming and keep failing to match. - Whether a cleaning was enough cannot be told by looking at the cleaned count; it can be told only by examining the unmatched records one by one. - Case conversion depends on the language: the locale-insensitive conversion failed to match 15 of the 48 names, and the conversion that applies the Turkish rule matched them all. - Of three versions that produce the same match, only one lets the 40 correction decisions be read cell by cell; in the manual fix both the decision and the raw data are lost. ## Next Step Now that the matching is in place, the dates of the visit records can be used as well. The next step asks a question that looks simple: how many days passed between the opening and the closing of a fault record. A date in a cell is a number, and the displayed format is not that number itself; the difference between two dates is a subtraction, but whether the endpoints are included is a decision. The next lesson computes the mean resolution time of the same record set with two endpoint decisions, measures what "a month later" means at month end, and counts how many of the dates written as text can be read as two different days.