Split cells in Excel

The Split Text tool is part of Text Toolkit for Excel. With its help you can separate a column of text into multiple columns or rows. Easily split data by any character, string, or mask.

Before you start

We care about your data. The add-in will back up your file if you select the corresponding option.

How to split cells in Excel by characters

Use the Split by Characters option if you need to separate data in a column at each occurrence of the delimiters you pick or enter.

Click the Split Text icon on the Ablebits Data tab in the Text group and select Split by Characters:
Split Excel cells by chars.

You will need to make a few adjustments to the add-in's options at first:
How to split cells by characters.

  1. Select the column with the records you want to split and you will see its address in this field.
  2. Click the Expand selection icon to get the whole table highlighted.
    Note. The tool can split only one column at a time.
  3. The values you want to split may be separated by standard delimiters, such as comma, space, semicolon, and/or line break. Also, you can enter any other character into the Custom field.
  4. If the values you want to split are divided by several successive symbols, tick off the Treat consecutive delimiters as one checkbox.
  5. Decide how to pull your cells apart: by columns or by rows:
    Split Excel cells by columns or by rows.
  6. Excel doesn't let you cancel changes made by add-ins, which is why we recommend selecting the Back up worksheet option.

Сlick Split and see the divided text in new columns or rows.

How to split text in Excel by mask

Using the Split by Mask option, you can enter several delimiters used within cell contents to separate the necessary values in Excel.

Click the Split Text icon on the Ablebits Data tab in the Text group and select Split by Mask:
Start Split by Mask.

You will need to make a few adjustments to the options here as well:
How to split by mask in Excel.

  1. Pick the range with values that you need to pull apart and see the range address in this field.
  2. Use the Expand selection icon to get the whole table selected automatically.
  3. Enter the delimiters to help with splitting cells. Use asterisks (*) to indicate the position of your text.
    Tip. If asterisk is one of the delimiters, type the tilde (~) sign before a double asterisk: ~**.
  4. See if you want To split cells to columns or To rows.
  5. We recommend keeping the Back up the worksheet option selected.

Click the Split button and see the divided text in new columns or rows.

How to split cells in Excel by strings

Use the Split by Strings option to separate values based on a combination of characters.

Click the Split Text icon on the Ablebits Data tab in the Text group and select Split by Strings:
Start the Split by Strings tool.

Fine-tune the splitting options for the better results:
How to split by Excel string.

  1. Select the range of cell where you want to separate your data.
  2. If you want to highlight the entire table, click the Expand selection.
  3. Enter the strings. If values in the column of interest can be separated by more than one string, enter them one per line.
  4. See if you want to split cells to columns or to rows in a new column.
  5. Leave Back up the worksheet checked to have a safe copy of your data.

Click Split to get the results in new columns or rows.

Post a comment

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.