Earlier separators disappear
Use the last occurrence for both outputs, not the default first occurrence.
Extract both sides of the final separator
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
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.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:H12. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Use the last literal separator to separate a record prefix from its final field without losing earlier separators or repeated records.
THE QUICK ANSWER01
Use the last literal separator to separate a record prefix from its final field without losing earlier separators or repeated records.
ƒx
=LET(records,FILTER(A2:A9,A2:A9<>"",""),IF(SUMPRODUCT(--(A2:A9<>""))=0,"No records",HSTACK(TEXTBEFORE(records,$F$2,-1,0,0,"Review"),TEXTAFTER(records,$F$2,-1,0,0,"Review"))))| Field 1 | Field 2 |
|---|---|
| Path/0007 | retail |
| Review | Review |
| "" | end |
| start | "" |
| a/ | b |
| 0 | x |
| Å | β |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Last differs from first
Path/0007/retail has two separators. Splitting at the last gives Path/0007 and retail. A first-occurrence formula would lose 0007 from the prefix and leave it inside the final field.
Blank sides are legitimate
For /end the prefix is empty; for start/ the final field is empty. No delimiter gives Review in both columns. The separator-only record / has two empty text outputs and still counts as one two-column result.
Supported source and control
Each source cell is text up to 120 UTF-16 units or blank. F2 is a nonempty, case-sensitive text separator up to eight UTF-16 units without line breaks. Source order, Unicode text and duplicates are preserved. Numeric/Boolean source cells are left ungraded so identifier conversion is always deliberate.
Read the full step-by-step walkthrough
- Choose the records once
FILTER retains the nonempty source records in their original order. Repeated labels are separate records.
- Read the literal separator
F2 may contain one or several characters. Case matters; * is ordinary text rather than a wildcard.
- Search backward from the end
An instance number of -1 selects the final occurrence. The prefix for Path/0007/retail is Path/0007.
- Return the remaining field as text
TEXTAFTER uses the same final boundary. A field such as 0001 must keep all four characters.
- Declare missing and empty states
A missing separator produces Review. A leading or trailing separator has a real empty side; that is different from no separator.
- Keep the two outputs aligned
HSTACK joins two arrays derived from the same filtered record list. Enter only G5 and keep the two-column spill clear.
Things to watch
An empty side is marked Review
A separator at the beginning or end is a match. Review is reserved for no separator.
Repeated records are combined
This is row-wise extraction. Do not apply UNIQUE or sort the source.
About this example
The original bounded datasets, typed outputs and independent rules are checked in the browser engine. Native Excel/Sheets recalculation, editing and save/reopen are separate pending checks. Regex uses the syntax shared by the pinned browser engine and PCRE2; full PCRE2 support is not claimed.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.