Excel Practice
Lessons Lesson 72

Splitting Text: Putting It Together

About this lesson

Any delimited line splits three ways. TEXTBEFORE for its first part. TEXTAFTER with -1 for its last. TEXTBEFORE around TEXTAFTER for a part in the middle.

By the end you can

  • split any delimited line into its first, middle and last parts.
  • clean a split part with SUBSTITUTE in the same formula.

Practises TEXTBEFORE TEXTAFTER SUBSTITUTE TRIM

The idea

Three functions, and the whole module fits in one line of a log. First part: TEXTBEFORE. Last part: TEXTAFTER with -1. Anything in between: TEXTAFTER to drop what is before it, then TEXTBEFORE to drop what is after. Write the delimiter exactly as it appears, spaces and all.

For a line with many parts, Excel 365 also has TEXTSPLIT. It spills every part into its own cell at once. It is not in this course, because a spilled result cannot be typed into one cell and marked. The pattern here is what TEXTSPLIT does, one part at a time.

The mistake to watch for

Writing the delimiter without its spaces. Splitting on a bare bar leaves a space on the front of every part. A value with a leading space matches nothing. Write the delimiter exactly as it appears in the text, spaces included. And reach for the last separator with -1, not the second. Then an extra field in the middle does not break the message.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Each log line has three parts separated by a space, a bar and a space. In B2, take the date: everything before the first separator.

To begin, type it exactly:

=TEXTBEFORE(A2," | ")

B2
Row A B C D E F G
1 Log line Date Level Message
2 15 Mar 2024 | ERROR | disk full
3 15 Mar 2024 | INFO | backup done
4 16 Mar 2024 | WARN | 3 retries
5
6
7
8

Every step

  1. 1

    Each log line has three parts separated by a space, a bar and a space. In B2, take the date: everything before the first separator. To begin, type it exactly: =TEXTBEFORE(A2," | ").

    Hint. " | " as the delimiter.

  2. 2

    In D2, the message: everything after the last separator.

    Hint. Instance -1.

  3. 3

    In C2, the level in the middle. Take what follows the first separator, then what comes before the next one. TEXTBEFORE around TEXTAFTER.

    Hint. TEXTBEFORE outside, TEXTAFTER inside.

  4. 4

    Read =TEXTAFTER(A4," | ",2) and say what it returns.

    Hint. The second bar.

  5. 5

    In E2, write a formula using SUBSTITUTE and TEXTBEFORE. It should give the date with its spaces turned into hyphens: 15-Mar-2024.

    Hint. SUBSTITUTE outside, TEXTBEFORE inside.