Remove file extension from a filename (multi-dot safe)

Why splitting on the first dot corrupts versioned filenames

Real filenames frequently contain multiple periods: "quarterly.forecast.v2.xlsx". Splitting on the first dot with TEXTBEFORE(A2, ".", 1) truncates the name to "quarterly" and discards ".forecast.v2".

Setting instance_num = -1 searches backward from the right end of the string: TEXTBEFORE(A2, ".", -1) preserves "quarterly.forecast.v2", and TEXTAFTER(A2, ".", -1) isolates "xlsx".

Multi-dot filename stem and extension formulas

Wrap TEXTBEFORE and TEXTAFTER directly in IFERROR BEFORE applying LOWER so extensionless files like "Makefile" return cleanly.

=IFERROR(TEXTBEFORE(A2, ".", -1), A2)

Extract normalized lowercase file extension

Placing IFERROR inside LOWER ensures missing dots return "" instead of "#n/a".

=LOWER(IFERROR(TEXTAFTER(A2, ".", -1), ""))

Inputs and interpretation

Pass a basename, not a full path or URL; directory dots and query strings need separate parsing. This removes only the final suffix, so archive.tar.gz becomes archive.tar. Leading-dot files and trailing dots need an explicit policy. An extension is a label and does not verify the file’s contents or safety. The inner IFERROR placement also avoids Arte Sheets coercing an error value through LOWER.

Further reading

• Microsoft Support — TEXTBEFORE function