How to Split Text in Excel

With Text Toolkit for Microsoft Excel

With the Text Toolkit for Excel, you can easily split cells by characters, strings, or mask. In this manual, find out how to use the Split Text utility.

  1. To run Text Toolkit in Excel, go to the Home tab and click the Text Toolkit icon:
    Click Text Toolkit.
  2. Select Split Text:

    Select Split Text.

  3. You will see the Split Text pane. Select your options and click the Split button:

    The Split Text pane.

  1. Select the range of cells which you want to split.
  2. You can split cells by strings or characters or by mask.

    Select the Split by strings or characters radio button if text in your cells has some characters or strings by which you want to split your cells, and these characters may appear in different order and combinations.

    For instance, if you've got "AxyByxC" and want to split it into three columns, you enter "x" and "y" into the input field and get the result " A | B | C ".

    Note. Please note, if you enter delimiters separately and the add-in finds their combinations in the cells, it will add additional blank columns between the divided data. To avoid this, tick the Treat consecutive delimiters as one checkbox.
  3. The Treat consecutive delimiters as one checkbox will be of use if you've got several delimiters and want them to be recognized separately as well as in combinations.

    For instance, your data set is "AxByCxyDyxE" and you want it to be split by "x", "y", and all their combinations: "xy" and "yx". You enter "x" and "y" into the input field and check the Treat consecutive delimiters as one box:

    Treat consecutive delimiters as one.

    The cell will be divided according to all the relevant combinations:

    Divided cell.

  4. Split by mask is useful if you need to split a list of homogeneous strings into some elements or substrings.

    For instance, you've got the same "AxByCxyDyxE" data set and want to get the same A | B | C | D | E result. In this case, you'll have to enter all the delimiters in the order they appear in the string and divide them by the * sign: *x*y*xy*yx*:
    Split by mask.

    So, with the Split by mask option, it is very important to indicate all the delimiters in the accurate order of their appearance in your cells and separate them by the * sign.

  5. You can check Case-sensitive, and, in this case, if you want to split text by the "E" letter and enter "e" into the input field, the tool won't split your cells correctly as it will be searching for "e" only in lower case. If you do not check this box, the case of the character entered as a separator won't matter.
  6. If you tick the Enable Undo box, the Undo button will appear:
    Tick Enable Undo to be able to Undo.
    As soon as you hit Split, the Undo button will become clickable and you'll be able to undo the changes made by the add-in:
    Undo the changes.