The prompt
Score 10 of 10, Excellent
Use it when A column mixes several pieces of information in one cell, such as codes, names and dates, and you need them pulled out into their own columns.
- {{tool}}
- {{column_location}}
- {{parts_wanted}}
- {{sample_values}}
Full prompt and why it works
Prompt
Write formulas that extract or split the text in the column described below into separate columns, for me to paste into {{tool}} and fill down, so I can filter and sort by each part. Column name and location (sheet, column letter, first data row): {{column_location}} What I want pulled out, one new column each: {{parts_wanted}} At least 15 real values from the column, including the messiest ones: """ {{sample_values}} """ Produce: 1. For each new column: the formula for the first data row, a one-sentence explanation of how it works, and the pattern it assumes, such as "the code is always the text before the first hyphen". 2. A test table with the column Sample value, then one column per part, showing what each formula returns for every sample value I gave, so I can see where it works and where it does not. 3. The values the formulas cannot handle, with the reason and what I could do about them: a second formula, a manual fix, or a change at the source. Use only functions available in {{tool}}, and prefer the simplest that works, for example TEXTBEFORE and TEXTAFTER over nested FIND and MID where my version has them, because simpler formulas are easier for the next person to fix. If a regular expression is the only reliable way, give it and explain each part in plain words. Return a blank, not an error, when a part is missing from a cell. Do not change or trim the original column. Base the patterns on my sample values only. If two samples follow patterns that would need different rules, do not choose between them; show both and ask which is correct. If you cannot tell which version of {{tool}} I have and it changes the answer, give the formula for the newest version and an alternative for older ones.
Variables
- {{tool}}
- Your spreadsheet and version, for example "Excel for Microsoft 365", "Excel 2016" or "Google Sheets".
- {{column_location}}
- Where the column is, for example "sheet Orders, column C, data from row 2".
- {{parts_wanted}}
- Each part you want, for example "region code, store number, and the date at the end".
- {{sample_values}}
- At least 15 values copied from the column, one per line, including unusual ones.
Why it works
- An example. A test table runs every formula on your own sample values, so you see where it works before filling down 10,000 rows.
- If unsure. Samples that follow different patterns are shown both ways with a question, instead of one rule that silently mangles half the column.
- What good looks like. The simplest function your version supports is preferred, because the next person has to be able to fix the formula.