Lookup & Reference
Find the first cell that does NOT begin with a comment or test prefix
Skip variable-length metadata/comment header lines ("#", "//", "TEST-") in imported logs to locate the first real transaction row.
Automatically skip variable-length "#" or "TEST-" preamble lines in raw imports!
Some text exports contain a preamble marked with a known prefix. This example skips one configured prefix and blank lines; it does not parse or validate a banking or laboratory file format.
Testing each row's prefix in a lightweight helper flag B2 = (A2 <> "") * (LEN(TRIM(A2)) > 0) * (LEFT(TRIM(A2), LEN($D$2)) <> $D$2) marks 0 on blank/whitespace/comment preamble lines and 1 on real records. XMATCH(1, B2:B6) returns the exact relative starting row, and INDEX(A2:A6, XMATCH(1, B2:B6)) extracts the first live transaction!
Only the prefix in D2 is excluded. Changing it replaces the rule; it does not keep excluding # as well. TRIM handles ordinary spaces, not all Unicode whitespace. A remaining line is only a candidate record, not a validated transaction. Enter a nonempty prefix.
| Raw Log Line (A) | Prefix Check <> "#" & Non-Blank (B) | Audit Action |
|---|---|---|
| "# Generated 2026-10-30" | 0 (Starts with "#") | Skipped |
| "# Currency: USD" | 0 (Starts with "#") | Skipped |
| "" (Blank spacer) | 0 (Empty line) | Skipped |
| "TXN-9001 | $1,420.00" | 1 (First valid record!) | Matched at relative row 4! |
Locate and extract the first non-comment, non-blank record
XMATCH(1, B2:B6) finds the exact 1-based position where live records start.
=IF(COUNTBLANK(D2)>0,"Enter prefix",IFERROR(INDEX(A2:A6,XMATCH(1,B2:B6)),"No matching rows"))Count candidate records without an empty-array error
SUM of the numeric helper flags returns zero when no rows qualify. FILTER can display the rows; its empty-result placeholder must not be counted as a record.
=SUM(B2:B6)Try the example
Use the original values or change them to test your understanding.