Organize information

Split combined fields into Excel columns

Separate names, addresses, or codes using verified delimiters and exception flags

6 min setupTested with:ChatGPTReviewed: 2026-08-28

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.

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.