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 field. 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 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 group: 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 a double asterisk: ~**.
  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 Text icon and expand the Split by strings group: 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.

Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard
 
 
Excel add-ins and Outlook tools - Ablebits.com