Do you have text in an Excel cell, but don‘t know how to cut it cleanly? TEXTSPLIT is here to make your life easier.

When a cell is overflowing with text, Excel pulls out its digital scissors.

TEXTSPLIT will save you time by slicing your textual data in Excel in the blink of an eye.

How does it works

La fonction FRACTIONNER.TEXTE fonctionne de la manière suivante :

The TEXTSPLIT function works as follows:

=TEXTSPLIT(text; col_delimiter; [row_delimiter]; [ignore_empty]; [match_mode]; [pad_with])

  • text: Text to be cut out.
  • col_delimiter: element that distinguishes the text to be cut.
  • row_delimiter : An element that indicates the line change (optional argument).
  • ignore_empty : Allows you to ignore empty or unempty cells (optional argument, False by default).
    • True: Ignore consecutive delimiters.
    • False (default): Create an empty cell.
  • match_mode: specify whether the case must be respected (upper/lowercase) and thus the reported text will have the same case as the original (optional argument, 0 by default).
    • 0: whether case must be respected
    • 1: if the case doesn‘t matter
  • pad_with : The value to be displayed if no value is found (optional argument, default #N/A).

Tip : TEXTSPLIT automatically populates columns or rows according to your delimiters. Consider inserting it into an empty area to avoid collisions.

Examples of use

This powerful feature can split texts and recreate a simple or more complex table.

Split text across multiple columns

If you use only the first 2 arguments, you will be able to separate your text according to a defined delimiter.

In the examples above, the text is separated with respect to different delimiters.

  • 1st example, we separate the text that surrounds “–“: we keep the spaces next to the hyphen.
  • 2nd example, spaces are the delimiters.
  • 3rd example, semicolons are the delimiters.

Note: Delimiters could also be text.

Split text across multiple columns and lines

In the following examples, let‘s explore the different arguments of the function.

In this example, the delimiter is the period, and the 3rd argument notifies that the @ symbol indicates a line change.

In the first 3 examples above:

  • The delimiter is the comma
  • The hyphen indicates the change of line

You can see that if 2 delimiters follow each other with no value, Excel creates an empty cell.

In the 2nd example, we put True in the 4th argument (Ignore if empty) and put the word “Missing“ in the 6th argument (fill with).

The 3rd and 4th examples show the difference between case (uppercase/lowercase) and non–case.

Note: if your delimiters are multiple or irregular, remember to test the match_mode parameter.

Conclusion

TEXTSPLIT is a little gem to save time and avoid convoluted formulas. To be tested without moderation in your next tables!