Text
Parse XML tags and JSON webhook fields with formulas (TEXTAFTER & TEXTBEFORE)
Extract text from controlled XML-like tags and compact JSON string fields using delimiter formulas. These are not general parsers.
Delimiter extraction for controlled inputs
TEXTAFTER and TEXTBEFORE can demonstrate extraction when the source format is known and tightly controlled. The XML example requires an exact opening tag without attributes and a corresponding closing tag. The JSON example requires an exact compact key:"value" pattern.
These formulas do not validate XML or JSON. They do not handle XML namespaces, attributes, entities, nested same-name tags or CDATA; the JSON formula does not handle escaped quotes, optional whitespace, nested paths, non-string values or duplicate-key semantics.
For arbitrary logs and actual application payloads, parse the data with an appropriate XML/JSON parser before importing fields. A Not Found result here can mean unsupported formatting or a source error, not necessarily an absent field. TEXTBEFORE/TEXTAFTER require a spreadsheet version that supports them.
| Payload Format | Raw Log Cell | Target Tag / Key | Extracted Value |
|---|---|---|---|
| XML Element | <order><id>ORD-904</id><total>249.50</total></order> | "id" / "total" | "ORD-904" / "249.50" |
| JSON String Field | {"id":"evt_88","status":"succeeded","plan":"pro"} | "status" | "succeeded" |
| Missing Tag / Key | Same payload | "coupon" | "Not Found" |
Extract between exact plain tags
Returns raw text between the first matching delimiters under the controlled-format assumptions. It does not decode XML entities.
=IFERROR(TEXTBEFORE(TEXTAFTER(A2, "<" & B2 & ">"), "</" & B2 & ">"), "Not Found")Extract quoted string property from JSON log payloads
Works only for the shown compact, unescaped string-field form. CHAR(34) supplies a literal double quote.
=LET(q, CHAR(34), IFERROR(TEXTBEFORE(TEXTAFTER(C2, q & D2 & q & ":" & q), q), "Not Found"))Try the example
Use the original values or change them to test your understanding.