How to split cells in Excel

Before you start

We care about your data. The add-in will back up your worksheet 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:

The tool pane will show up:

1. Select a cell or 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 column selected.
   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 to be split are divided by several successive symbols and you want the add-in to process them all as one delimiter, select the Treat consecutive delimiters as one option.
5. If you want to preserve delimiters in the split records, select the Keep delimiters in resulting cells option and then choose whether you want to have them At the beginning or At the end of the cell contents.

   For instance, if we have "ab.cd.ef" in a cell, split it by "." and do not check this box, we will get ab | cd | ef

   If we tick At the beginning, we'll have ab | .cd | .ef

   If we tick At the end, the result will be ab. | cd. | ef

6. Decide how to split your cells: by columns or by rows:
   
7. Excel doesn't let you cancel changes made by add-ins, which is why we recommend selecting the Back up this worksheet option.

Click Split.

How to split text in Excel by mask

The Split by Mask option allows you to enter several delimiters which are 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:

You will need to make a few adjustments in the tool pane:

1. Select a cell or column with the records you want to split, and you will see its address in this field.
2. Use the Expand selection icon to get the whole column 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 (~) before a double asterisk: ~**.
4. Choose whether you want to split cells to columns or to rows.
5. We recommend keeping the Back up this worksheet option selected to have a safe copy of your worksheet.

Click the Split button.

How to split cells in Excel by strings

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

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

Fine-tune the splitting options in the tool pane:

1. Select a cell or column with the records you want to split, and you will see its address in this field.
2. If you want to select the entire column, click Expand selection.
3. Enter the strings. If values in the column of interest can be separated by more than one string, enter them each into a new line.
4. If you want to preserve delimiters in the split records, select the Keep delimiters in resulting cells option and then choose whether you want to have them At the beginning or At the end of the cell contents.

   For instance, we have "(A)12(a)34(B)56(b)78" in a cell and we split it by the following strings:
   (A)
   (a)
   (B)
   (b)
   If we do not check this box, we get
   12 | 34 | 56 | 78

   If we tick At the beginning, we have (A)12 | (a)34 | (B)56 | (b)78

   If we tick At the end, the result is 12(a) | 34(B) | 56(b) | 78

5. Choose whether you want to split cells to columns or to rows.
6. Leave Back up this worksheet checked to have a safe copy of your data.

Click Split.