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

By Ali, Founder, Excel Lessons

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.

Read more about this

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, split by a bar.

In B2, type:

=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, split by a bar. In B2, type: =TEXTBEFORE(A2," | ").

    Hint. The delimiter is a space, a bar, then a space.

  2. 2

    In D2, take the text after the last separator with TEXTAFTER.

    Hint. Use -1 as the instance.

  3. 3

    A middle part needs TEXTBEFORE around TEXTAFTER. In C2, get the level between the two separators. Shape: =TEXTBEFORE(TEXTAFTER(cell,"delimiter"),"delimiter").

    Hint. Write TEXTAFTER first, then wrap it in TEXTBEFORE.

  4. 4

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

    Hint. The second bar.

  5. 5

    In E2, turn the date's spaces into hyphens. Shape: =SUBSTITUTE(TEXTBEFORE(cell,"delimiter")," ","-"). That gives 15-Mar-2024.

    Hint. Write TEXTBEFORE first, then wrap it in SUBSTITUTE.