Split cells in Excel

How to quickly separate data in Excel

Enter any combination of characters to split text in the selected column

Text Toolkit allows splitting cells by character, by mask, or by strings in Excel 2016, 2013-2007. You can choose to split text to columns or rows and be sure that your data will not be overwritten.

Free Download
7-day fully functional version

How to split cells in Excel by character

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

  1. Run the Split Text tool by clicking on its icon under Ablebits Data tab in Excel: Run the Split Text tool by clicking on its icon
  2. Select the column with records you want to split. You will see the address of the selected range at the top of the pane.
  3. You can separate cell contents in excel by standard delimiters, such as comma, space, semicolon, and/or line break, or enter any character into the Custom Separate cell contents in excel by comma, space, semicolon, or line break
  4. If the values you want to split are separated by several successive symbols, tick off the Treat consecutive delimiters as one check-box.
  5. You can also choose to separate cell data to columns or to rows. You can split cell contents to columns or to rows
  6. Excel doesn’t let you cancel changes made by add-ins, which is why we recommend selecting the option to Back up worksheet.
  7. Сlick Split and to see the result in new columns, or in a new column depending on the selected destination.

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.

  1. Run the Split Text tool by clicking on its icon and expand the Split by mask Enter the delimiters you want to be used for splitting cells
  2. Select the range with values that you need to pull apart, the address will be displayed at the top of the pane.
  3. Enter the delimiters you want to use for splitting cells. Use asterisks to indicate the position of text.
    Tip. If asterisk is one of the delimiters, type the tilde sign before it: ~*
  4. Choose if you want to split cells to columns or to rows. We also recommend keeping the Back up the worksheet option selected.
  5. Click the Split button and see the result in a new column or columns.

How to split cells in Excel by strings

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

  1. Select the range where you want to separate data cells.
  2. Click the Split Texticon and expand the Split by strings Enter the strings one per line
  3. If values in the selected column can be separated by more than one string, enter them one per line.
  4. Choose if you want to split cells to columns or to rows in a new column.
  5. Click Split to get the results.

With the Split Text tool you can separate a column of text into multiple columns. Easily split data by any character, strings, or enter a mask for dividing values.

 
Contact us
 
  Publish your message on our support forum
 

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Excel add-ins and Outlook tools - Ablebits.com