AI_EXTRACT
Extract structured data from unstructured text using AI
=AI_EXTRACT(text, schema)Retourne : string[][]
Aperçu
AI_EXTRACT turns unstructured text into clean, structured spreadsheet data using AI. Give it any block of text — a product listing, an email, a news article, a legal paragraph — along with a description of what you want extracted, and it returns a neatly organized table with headers and rows. This eliminates the hours of manual copy-paste work that knowledge workers spend every day pulling specific data points out of messy, inconsistent text sources.
Paramètres
| Paramètre | Type | Requis | Description |
|---|---|---|---|
text | string | Oui | The unstructured text to extract data from. Can be a cell reference, a range, or a string. |
schema | string | Oui | A natural language description of the data fields to extract. Example: "name, email, phone number, company". |
Exemples
Extract contact info from an email
Extracts contact details from an email body stored in cell A2.
=AI_EXTRACT(A2, "name, email, phone number, company")Entrée
| Hi, I'm Sarah Chen from Acme Corp. Reach me at sarah@acme.com or (555) 123-4567. |
Sortie
| Name | Phone Number | Company | |
| Sarah Chen | sarah@acme.com | (555) 123-4567 | Acme Corp |
Extract product details from a listing
Pulls structured product data from a free-text product description.
=AI_EXTRACT(A2, "product name, price, color options, material")Entrée
| The Aurora Desk Lamp ($89.99) is available in matte black, brushed gold, and silver. Made from recycled aluminum with a weighted steel base. |
Sortie
| Product Name | Price | Color Options | Material |
| Aurora Desk Lamp | $89.99 | Matte Black, Brushed Gold, Silver | Recycled Aluminum, Steel |
Parse job posting requirements
Extracts key hiring criteria from a job posting text to build a structured comparison sheet.
=AI_EXTRACT(A2, "job title, required years of experience, programming languages, salary range, location")Sortie
| Job Title | Required Years of Experience | Programming Languages | Salary Range | Location |
| Senior Frontend Engineer | 5+ | TypeScript, React, GraphQL | $140K-$180K | Remote (US) |
Extract dates and events from meeting notes
Converts rambling meeting notes into a structured action item tracker.
=AI_EXTRACT(A2, "date, event, owner, status")Sortie
| Date | Event | Owner | Status |
| Jan 15 | Launch landing page | Maria | In Progress |
| Jan 22 | Send email campaign | David | Not Started |
| Feb 1 | Review Q1 metrics | Team | Planned |
Extract financial data from a report paragraph
Turns a narrative financial summary into a structured data table for analysis.
=AI_EXTRACT(A2, "metric, value, change vs last quarter, trend direction")Sortie
| Metric | Value | Change vs Last Quarter | Trend Direction |
| Revenue | $4.2M | +12% | Up |
| Operating Margin | 23% | -2pp | Down |
| Customer Count | 1,250 | +85 | Up |
Cas d'Usage
Sales Lead Processing
Automatically extract contact details, company names, and deal sizes from inbound email inquiries or LinkedIn messages, populating your CRM spreadsheet without manual data entry.
Competitive Intelligence Gathering
Pull pricing, features, and positioning data from competitor product pages and press releases into a structured comparison matrix for strategic analysis.
Legal Document Review
Extract key clauses, dates, party names, and obligation terms from contracts and legal documents, building a searchable database of contractual commitments.
Academic Research Data Collection
Parse research paper abstracts to extract methodology, sample size, key findings, and conclusions into a structured literature review spreadsheet.
Real Estate Listing Analysis
Extract property details — bedrooms, bathrooms, square footage, price, neighborhood — from free-text listing descriptions to build a comparable properties database.
Supply Chain Document Processing
Parse shipping manifests, invoices, and customs documents to extract item descriptions, quantities, weights, and origin/destination information into standardized tracking sheets.
Conseils Pro
Be specific in your schema description. Instead of "info", write "company name, founding year, headquarters city, number of employees". Detailed schemas produce dramatically better results.
Chain AI_EXTRACT with AI_SCRAPE for a powerful web-to-spreadsheet pipeline: scrape raw text from a URL, then extract exactly the fields you need into clean columns.
If extraction quality is inconsistent, add format hints to your schema: "price (in USD, e.g. $99.99)" or "date (in YYYY-MM-DD format)". This helps the AI normalize the output.
Use AI_EXTRACT to validate data entry by extracting fields from a "notes" column and comparing them against manually entered values in adjacent columns.
The power of AI_EXTRACT lies in its schema parameter, where you describe in plain English what fields you want. You are not limited to predefined extraction patterns. Need to pull company names, revenue figures, and founding dates from investor reports? Just say so. Want to extract ingredient names, quantities, and units from recipe text? Describe those columns. The AI understands context and semantics, so it can extract data even when the source text uses varying formats, abbreviations, or natural language phrasing that would break traditional regex-based extraction.
AI_EXTRACT is particularly effective when combined with other Unlimited Sheets functions. Use AI_SCRAPE to pull raw text from a webpage, then pipe the result into AI_EXTRACT to structure it. Or use UNLIMITED_AI to generate content, then pass it through AI_EXTRACT to verify it contains all required elements. The function returns a 2D array with headers in the first row, making the output immediately usable for filtering, pivot tables, VLOOKUP, and other native Sheets operations.
Erreurs Courantes
#ERROR! Text is emptyCause : The text parameter is empty or references an empty cell.
Solution : Ensure the cell referenced in the text parameter contains text. Check for leading/trailing spaces that might make the cell appear empty.
#ERROR! Could not extract dataCause : The AI could not identify any of the requested fields in the provided text.
Solution : Check that your text actually contains the information described in your schema. Simplify your schema or provide more text context.
#VALUE! Schema is requiredCause : The schema parameter was not provided or is empty.
Solution : Add a schema string describing the fields to extract. Example: "name, email, phone".
Questions Fréquentes
The schema is a plain English description of the fields you want extracted. Simply list the column names separated by commas, like "name, email, company". You can also be more specific: "company name (string), annual revenue (number in USD), founding year (4-digit number)". The more specific your schema, the more accurate the extraction.
Yes. If your text contains information about multiple entities (e.g., a list of contacts or products), AI_EXTRACT will create a row for each entity it identifies. The output will have a header row followed by one row per record.
If the text does not contain information for a particular field in your schema, the cell will contain "N/A" or be left empty. The function will still extract whatever fields it can find rather than failing entirely.
The input text can be up to the model's context window limit (approximately 128K tokens for GPT-4o). In practice, Google Sheets cells can hold up to 50,000 characters. For longer documents, split them into chunks or extract from the most relevant section.
For structured, consistent formats (like URLs or phone numbers in a standard format), regex is faster and cheaper. AI_EXTRACT excels when the text format varies, uses natural language, or requires semantic understanding. For example, extracting "sentiment" or "key arguments" from free text is impossible with regex but trivial with AI_EXTRACT.
Yes, the underlying AI models support dozens of languages. AI_EXTRACT can process text in Spanish, French, German, Japanese, Chinese, and many more. You can even ask it to extract fields and output column headers in a different language than the source text.
Fonctions Associées
Commencez à utiliser AI_EXTRACT aujourd'hui
Installez Unlimited Sheets pour obtenir AI_EXTRACT et 41 autres fonctions puissantes dans Google Sheets.
