Organize information

Extract names, emails, or codes in Excel

Build a text formula from real delimiters without assuming every row has the same shape

5 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.

Text to extractObserved input patternsExcel version and rules

Your prompt

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

Create an Excel formula to extract text.

Desired result:
[goal]

Observed input patterns:
[patterns]

Excel version and exception rules:
[version]

Identify the delimiter or positional rule and test whether it is stable across the supplied values. Do not assume all names have two words or all codes have fixed length unless stated. Return a modern formula, a legacy-compatible alternative when relevant, and tests for missing delimiters, repeated delimiters, leading/trailing spaces, blanks, and non-ASCII text. Keep malformed values visibly flagged rather than silently truncating them.

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

Text to extract: From A2 values formatted as 'Full Name <email>', extract the email into B2.
Observed input patterns: Normal: Ana Pérez <ana@example.com>; Chinese name: 王小明 <xiaoming@example.cn>; leading spaces; blank; malformed value without angle brackets; one row contains two '<' characters.
Excel version and rules: Microsoft 365 supports TEXTBEFORE and TEXTAFTER. Blank input should stay blank. Any malformed value should return 'Review'.

Example output

The stable rule is text between one “<” and the following “>”. Because repeated delimiters are considered malformed, count them before extracting.

B2 formula =IF(TRIM(A2)="","",IF(AND(LEN(A2)-LEN(SUBSTITUTE(A2,"<",""))=1,LEN(A2)-LEN(SUBSTITUTE(A2,">",""))=1),TRIM(TEXTBEFORE(TEXTAFTER(A2,"<"),">")),"Review"))

The two length differences count opening and closing brackets. Only one of each allows extraction; TRIM removes surrounding spaces without changing accented or Chinese characters.

Legacy approach: use MID with FIND after the same bracket-count checks: TRIM(MID(A2,FIND("<",A2)+1,FIND(">",A2)-FIND("<",A2)-1)).

Tests: Ana Pérez and 王小明 return their full email addresses; blank returns blank; missing brackets and two opening brackets return Review. Also verify that “>” occurs after “<”; if reversed brackets exist in the data, add that condition before rollout.

Why this works

1
Observed patterns prevent a formula designed for one clean example from corrupting irregular rows.
2
A visible malformed-input result makes later cleanup auditable.