Text Functions
Sum, Average & Count Comma-Delimited Numbers Inside One Cell (TEXTSPLIT)
Parse comma- or pipe-delimited numeric lists packed inside a single cell (such as "120, 85, 95, 140") into real numbers using --TEXTSPLIT(B2, ",") and aggregate them with SUM, AVERAGE, MIN, and MAX.
Aggregate CSV number lists exported inside a single spreadsheet cell!
CRM exports, IoT telemetry payloads, and survey tools frequently pack multiple readings into one comma-separated text cell—such as "120, 85, 95, 140"! Calling TEXTSPLIT(B2, ",") splits that string into a 4-element horizontal array {"120", " 85", " 95", " 140"}, and here is the crucial trap: TEXTSPLIT always returns TEXT strings, which SUM and AVERAGE silently ignore as zero!
Prefixing TEXTSPLIT with the double-unary minus operator --TEXTSPLIT(B2, ",") coerces every token (even tokens with leading spaces!) into genuine numbers {120, 85, 95, 140} in a single step—letting SUM, AVERAGE, MAX, MIN, and COUNT aggregate packed lists without helper columns!
| Packed Cell Text (B2) | Raw TEXTSPLIT Result | Coerced --TEXTSPLIT Array | SUM & AVERAGE |
|---|---|---|---|
| "120, 85, 95, 140" | {"120"," 85"," 95"," 140"} (Text!) | {120, 85, 95, 140} | SUM = 440 | AVG = 110 |
| "45, 55, 80" | {"45"," 55"," 80"} | {45, 55, 80} | SUM = 180 | AVG = 60 |
| "" (Blank Cell) | #VALUE! if unguarded | Guarded by IF(TRIM(B2)="", 0, ...) | SUM = 0 |
Sum Comma-Delimited Numbers in One Cell (with Empty-Cell Guard)
Checks whether TRIM(B2) is empty first, then splits on "," and coerces text tokens to numbers via -- before summing.
=IF(TRIM(B2) = "", 0, SUM(--TEXTSPLIT(B2, ",")))Average & Count Delimited Numbers in One Cell
Computes the arithmetic mean of all comma-separated numbers inside B2.
=IF(TRIM(B2) = "", 0, ROUND(AVERAGE(--TEXTSPLIT(B2, ",")), 2))Try the example
Use the original values or change them to test your understanding.