Extract both sides of the final separator
Your goal: Use the last literal separator to separate a record prefix from its final field without losing earlier separators or repeated records.
Full task requirements
Enter one formula in G5 to report nonempty A2:A9 records in source order. Split each at the last case-sensitive literal separator in F2. Keep the whole prefix and final field as text; either side may be empty. With no separator, return Review in both columns. Ignore genuinely blank/empty cells; space-only records remain. If no records remain, return No records.
Extract the last field while keeping preceding separatorsKeep the nonempty original records
F2 is the literal, case-sensitive separator. Keep each source record in its original row.
| # | ||
|---|---|---|
| 1 | ||
| 2 | ||
| 3 | ||
| 4 | ||
| 5 | ||
| 6 | ||
| 7 | ||
| 8 | ||
| 9 |
Step 1 of 8
Keep the nonempty original records
Rules for this step
Choose every nonempty original record. A space-only record stays active, and repeated spellings remain separate physical records.