Pending shifts into the second field
Do not ignore empty fields. Consecutive delimiters preserve a position.
Split a pasted log into rows and fields
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. Split the text in A2 at | between fields and LF between records, normalizing CRLF pairs first. Preserve empty fields and records, spaces and text types. Pad shorter records with empty text to the widest record. An empty A2 returns No records. Up to eight records and three fields fit G5:I12; the exact result dimensions count.
Pasted source in A2
Use | between fields and a new line between records. This task has room for eight records and three fields per record.
The text matches the current A2 source.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:I12. 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
Turn a small literal pipe-delimited log into a rectangular report while keeping empty records, blank fields and leading zeroes.
THE QUICK ANSWER01
Turn a small literal pipe-delimited log into a rectangular report while keeping empty records, blank fields and leading zeroes.
ƒx
=IF(A2="","No records",TEXTSPLIT(SUBSTITUTE(A2,CHAR(13)&CHAR(10),CHAR(10)),"|",CHAR(10),FALSE,0,""))| Field 1 | Field 2 | Field 3 |
|---|---|---|
| T1 | 0007 | Open |
| T2 | "" | Pending |
| T3 | 0000 | Closed |
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
The original report
The original source becomes three rows: T1, 0007, Open; T2, empty text, Pending; and T3, 0000, Closed. The middle gap belongs to the second record and must stay in that field position.
Empty source differs from empty records
An empty A2 gives No records. By contrast, || followed by a line break and another || defines two records with three empty fields. A trailing line break also creates a final empty record under the stated rule. The checker examines dimensions, not just visible text.
A small literal format
A2 accepts text up to 1,200 UTF-16 units, at most eight LF-separated records and at most three pipe-separated fields per record. Quotes are ordinary data. If a field itself contains a pipe or line break, use a real structured file importer rather than assuming quote handling.
Keep conversion separate
Every field remains text, including 0, 2.50, TRUE and #N/A. Changing an identifier into a number or treating an error-looking label as an error changes this task. Source text stays beside the result and in the workbook.
Read the full step-by-step walkthrough
- Declare the two boundaries
Each | separates fields and each LF separates records. This is a literal log format, not a quoted CSV parser.
- Normalize Windows line endings
Replace a CRLF pair with one LF. A bare CR needs a separate rule and is outside this task.
- Keep empty positions
FALSE retains adjacent delimiters. T2 has an empty second field; shifting Pending left would change its meaning.
- Pad the short rows deliberately
The final empty-text argument fills positions missing from shorter records. It differs from the default error padding.
- Retain the text representation
0007, 0000 and 0 are identifiers in this task. Splitting does not authorize converting them into numbers.
- Check shape as well as values
The all-empty-fields case has two rows and three columns even though every result looks blank. Keep the full spill area free.
Things to watch
A source zero becomes numeric
The split result is text. Apply VALUE only in a different task where conversion is intended.
A blank final record disappears
A final line break is still a record boundary here. Do not trim or filter it away.
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.