How to 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.

Responses

Nubian-Elon Dupree says:
July 27, 2018 at 10:32 pm

Good Afternoon,
Non of these examples work for splitting a cell. All of the options presented are not in the MS Office Excel 2016.

Reply

Hello Nubian-Elon Dupree,
Thank you for your comment. The presented options are part of the Ablebits Text Toolkit add-in for Excel.

You can download the 7-day free trial version here.

Reply
Philip Wilson says:
September 5, 2019 at 1:07 pm

The custom option is not allowing me to type in any text to choose to split it by on the split by characters. Also on the by string it will not allow me to do it that way as well.

Reply
Katerina Bespalaya says:
September 5, 2019 at 1:46 pm

Hello Philip,

Sorry to hear that you've encountered this problem. The issue may occur due to the recent Windows update. We have already published a hotfix, please make sure that you have the latest version of Ultimate Suite installed on your computer, version 2018.5.2020.9438.

You can download and install the latest update using this link.

If you're still having any difficulties, please contact us at support@ablebits.com.

Reply

Ask a question (posted publicly)

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.
Privacy policy 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.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-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