=JSONEXTRACT

The =JSONEXTRACT() function scans a text string and extracts the valid JSON object or JSON array it finds, discarding any text around it. If valid JSON exists within the text, only the JSON portion is returned. If no valid JSON is found, the function returns nothing.

Syntax

=JSONEXTRACT(AnyTextContainingJSON)

Parameters

• AnyTextContainingJSON

  • Any text value that may include embedded JSON data (object or array).

How It Works

• The function works backwards from the end of the text.

• The last valid JSON object or array is identified and extracted.

• If the text wraps its JSON in a code block (the way an AI answer usually does), the contents of the last such block are used.

• If no valid JSON structure exists, the result is empty.

Usage Notes

• If the text holds more than one JSON structure, the last one is returned.

• Text before or after the JSON is discarded, including a sentence of commentary that an AI adds after its answer.

• Returns nothing if the text does not contain valid JSON.

• Supports both JSON objects and arrays.

• Formula function names are case sensitive and must be written in ALL CAPS.

---

Examples

Extracting a JSON Object from Mixed Text

=JSONEXTRACT("The API response was: {\"status\":\"success\",\"data\":{\"id\":123,\"name\":\"John\"}} and processing completed.")

Returns:

{"status":"success","data":{"id":123,"name":"John"}}

Extracting a JSON Array from Text

=JSONEXTRACT("Processing items: [\"item1\",\"item2\",\"item3\"] - batch completed")

Returns:

["item1","item2","item3"]

When the Text Holds Two JSON Objects

=JSONEXTRACT("First attempt: {\"id\":1} Corrected: {\"id\":2}")

Returns the last one:

{"id":2}

---

Common Use Cases

• Parsing API responses from log files

• Extracting configuration data from mixed content

• Processing webhook payloads

• Cleaning scraped web data containing JSON

• Parsing email content with embedded structured data

• Processing chat messages with configuration snippets

• Extracting metadata from file headers

---

Keywords

JSONEXTRACT, extract JSON, parse JSON from text, JSON parsing, API response parsing, webhook processing, structured data extraction, formula function