Split combined fields into Excel columns

Author: AILesson6 min setupTested with:ChatGPTReviewed: 2026-08-28

Quick answer

Separate names, addresses, or codes using verified delimiters and exception flags. Provide: Desired output fields, Representative source values, Excel version and allowed tools. Expected result: A non-destructive split method, validation columns, and exception queue.

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Desired output fieldsRepresentative source valuesExcel version and allowed tools

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Design a safe way to split a combined Excel field.

Desired output fields:
[target]

Representative source values:
[samples]

Excel version and allowed tools:
[tools]

Profile delimiters and structural variants before choosing a formula, Text to Columns, or Power Query. Do not split personal names by spaces or assume a fixed address length unless the data contract guarantees it. Preserve the original value. Return the recommended method, formulas or transformation steps, a ParseStatus/ReviewReason field, and tests for missing components, extra delimiters, quoted text, blanks, and multilingual values. Explain which rows cannot be parsed safely.

Try in Playground

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Desired output fields: Split A RawContact into B CustomerID, C DisplayName, and D Email. The documented format is CustomerID | DisplayName | Email.
Representative source values: C-101 | Ana Pérez | ana@example.com; C-102|王小明|xiaoming@example.cn; C-103 | Acme | Europe | sales@example.com; C-104 | Noor Ali | blank; blank row.
Excel version and allowed tools: Microsoft 365 formulas are preferred for an auditable first pass. Power Query is allowed later for repeat imports.

Example output

The documented format requires exactly two pipe delimiters. Preserve A and create E DelimiterCount: =LEN(A2)-LEN(SUBSTITUTE(A2,"|","")); F ParseStatus: =IF(TRIM(A2)="","Blank",IF(E2=2,"Parsed","Review")).

For Parsed rows:

B2: =IF($F2="Parsed",TRIM(TEXTBEFORE($A2,"|")),"")
C2: =IF($F2="Parsed",TRIM(TEXTBEFORE(TEXTAFTER($A2,"|"),"|")),"")
D2: =IF($F2="Parsed",TRIM(TEXTAFTER($A2,"|",2)),"")

C-101 and C-102 parse safely; Unicode names are preserved. C-103 contains three delimiters and goes to Review because it is unclear whether “Europe” belongs to the name. C-104 can parse only if the source literally retains the third empty field after the second pipe; otherwise it is Review. Blank rows remain Blank. Add field validations for CustomerID pattern and email structure, but do not change ParseStatus to Valid until both pass. For recurring imports, reproduce the same delimiter-count gate in Power Query and route error rows to a separate review query.

Why this works

1
Profiling variants prevents a convenient delimiter from being mistaken for a reliable schema.
2
Preserving the source and parse status makes partial extraction recoverable.

Check the result

Does every automatic split depend on a verified structural rule?
Are unparseable values retained intact for review?
Can recombining output fields reproduce the source meaning?

Use it with confidence

Frequently asked questions

Practical answers about when to use this recipe, what to provide, and where human review still matters

What should I prepare before using “Split combined fields into Excel columns”?

For “Split combined fields into Excel columns,” prepare Desired output fields, Representative source values, and Excel version and allowed tools. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.

When is the “Split combined fields into Excel columns” result not ready to use?

The result is not ready if it does not yet deliver the stated outcome—A non-destructive split method, validation columns, and exception queue—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.

Which AI tools have recorded tests for “Split combined fields into Excel columns”?

The published test record for “Split combined fields into Excel columns” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.

More ways to explore

Where this recipe fits

Text prompts Excel & spreadsheets

Keep the work moving

Text prompt

Spreadsheet insights

Excel & spreadsheets 6 min setup

Turn spreadsheet analysis into an executive summary

Convert verified metrics into decisions, changes, risks, and requests without overstating evidence

Open recipe

Text prompt

Spreadsheet insights

Excel & spreadsheets 6 min setup

Standardize category labels in Excel

Map known label variants to an approved vocabulary while exposing unknown values

Open recipe

Text prompt

Spreadsheet insights

Excel & spreadsheets 7 min setup

Reshape an Excel table between wide and long formats

Unpivot or pivot data while protecting record grain, identifiers, null meaning, and aggregation rules

Open recipe

AILesson · Recommended courses

Your next step: put AI to work

Go from understanding AI to getting things done. Practice prompting, reviewing, and improving results through interactive lessons for work and everyday life.

Explore all courses