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.
The prompt
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.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- Excel 365 / Excel 2016-2019 / Google Sheets
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Write an Excel formula to categorize records automaticallyBuilds the formula and lookup table that auto-assigns categories, bands, or tiers to each row based on your rules.
- Write a monthly IT and security report for leadershipTurns ticket, uptime, and security metrics into a short report showing trends, risks, and decisions needed.
- Write an analytics event tracking plan for a featureAn engineer-ready event spec with names, triggers, properties, and the questions each event answers.
- Write commentary for a dashboard or KPI reportWrites the 'so what' narrative executives need alongside a dashboard — for sales, marketing, HR, finance, ops, or support.