# Write an Excel formula to extract text, numbers, or dates from messy cells

Pulls the piece you need out of combined or messy cells, like codes, names, domains, or dates, with a formula that handles exceptions.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Advanced
- Tags: Data & Analytics, Write, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/write-an-excel-formula-to-extract-text-numbers-or-dates-from-messy

## Prompt

```text
Write an Excel formula to extract specific information from messy cells.

**What I want to extract:** [e.g. "the order number from a description", "first and last name", "the domain from an email", "the date inside a text string", "the number before 'kg'"]
**Sample cells:** [PASTE 10-20 real examples, including the awkward ones, and what the correct result is for each]
**My tool:** [Excel 365 / Excel 2016-2019 / Google Sheets]

Do this:
1. Describe the pattern you see in my examples and the exceptions that break it (missing delimiter, extra spaces, two matches in one cell, different lengths).
2. Pick the simplest reliable method for my version:
   - Excel 365: TEXTBEFORE, TEXTAFTER, TEXTSPLIT, and REGEXEXTRACT if available.
   - Older Excel: LEFT, RIGHT, MID with FIND or SEARCH, and SUBSTITUTE tricks.
   - Google Sheets: REGEXEXTRACT, SPLIT.
   - One-off jobs: Flash Fill or Text to Columns, if a formula is overkill.
3. Convert the result to the right type (VALUE for numbers, DATEVALUE for dates) so it can be summed or sorted.

Return:
1. The pattern and exceptions.
2. The formula for the first row, ready to fill down, wrapped in IFERROR with a visible fallback such as "CHECK".
3. A table showing each of my sample cells, the formula's result, and whether it matches the expected answer.
4. A short explanation of how the formula works.
5. An alternative method if the data is very inconsistent (e.g. Power Query split by delimiter).

Do not claim the formula works for every case - point out any sample it gets wrong.
```

## What to fill in

- Excel 365 / Excel 2016-2019 / Google Sheets

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.