Keep field boundaries and identifiers before converting text
Decide which separators, empty records and character rules define an imported text report before changing its values.
| Source | Rule | Result |
|---|---|---|
| T2||Pending | Keep empty field | T2, empty text, Pending |
| Path/0007/retail | Last slash | Path/0007 and retail |
| aa-0007 | ASCII shape, then uppercase | AA-0007, Ready, text 0007 |
| AB-1234 plus LF | Exact seven-character source | Review |
An empty field still has a position
For the literal log T1|0007|Open followed by T2||Pending, the second record has a missing middle field. Ignoring that empty position would move Pending into the identifier column. Splitting and deciding which records to discard are different operations.
Under this course rule, a trailing line break creates a final empty record. Two rows of three empty fields can look like an empty worksheet while still having a defined two-by-three result. Check dimensions as well as visible values.
=TEXTSPLIT(A2,"|",CHAR(10),FALSE,0,"")Choose the separator occurrence
Path/0007/retail has two slash characters. Extracting at the last one gives Path/0007 and retail. Extracting at the first one gives Path and 0007/retail. Neither answer is universally correct; the requested fields determine the rule.
A separator at the beginning or end creates an empty side. A missing separator needs a distinct review state. Preserve repeated records and source order unless a separate task asks for grouping or deduplication.
=TEXTAFTER(A2,"/",-1,0,0,"Review")A normalized label is not proof of validity
This course accepts a cleaned code made of two ASCII letters, a hyphen and four ASCII digits. Validate that source layout before converting its display to uppercase. A non-ASCII source that expands under uppercasing must not bypass the declared alphabet.
Use an exact length condition with the anchored pattern. Keep the accepted suffix as text: 0007 is a four-character identifier component, not the quantity seven. Ready records satisfy this local format rule; they have not been checked against an external registry.
=REGEXTEST(A2,"^[A-Za-z]{2}-[0-9]{4}$")*(LEN(A2)=7)Check the idea
A record is /end and the separator is /. Under a final-separator rule, which result preserves the source?
Try the idea
References
Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.